Power Bi Dax

Power BI DAX Skill

Execute and validate DAX queries against connected Power BI models.

Prerequisites

pipx install pbi-cli-tool
pbi-cli skills install
pbi connect

Executing Queries

# Inline query
pbi dax execute "EVALUATE TOPN(10, Sales)"

# From file
pbi dax execute --file query.dax

# From stdin (piping)
cat query.dax | pbi dax execute -
echo "EVALUATE Sales" | pbi dax execute -

# With options
pbi dax execute "EVALUATE Sales" --max-rows 100
pbi dax execute "EVALUATE Sales" --timeout 300       # Custom timeout (seconds)

# JSON output for scripting
pbi --json dax execute "EVALUATE Sales"

DAX Expression Limitations in CLI

When passing DAX as a -e argument, the shell collapses newlines into a single line. Simple expressions like SUM(Sales[Amount]) work fine, but multi-line DAX using VAR/RETURN breaks because the DAX parser needs line breaks between those keywords.

Why this matters: A measure like VAR x = [Total Sales] VAR y = [Sales PY] RETURN DIVIDE(x - y, y) will fail with a syntax error because the engine sees it as one continuous line without statement separators.

Workarounds (pick one):

# Option 1: Pipe from stdin (recommended for measures)
echo 'VAR TotalSales = SUM(Sales[Amount])
VAR TotalCost = SUM(Sales[Cost])
RETURN TotalSales - TotalCost' | pbi measure create "Profit" -e - -t Sales

# Option 2: Write to a .dax file and use --file (for queries)
echo 'EVALUATE
ROW("Result",
    VAR x = SUM(Sales[Amount])
    RETURN x
)' > query.dax
pbi dax execute --file query.dax

Single-line alternatives (preferred when possible):

For simple ratio/growth measures, use inline patterns instead of VAR/RETURN:

# Instead of: VAR x = SUM(...) / VAR y = SUM(...) / RETURN DIVIDE(x, y)
# Use inline DIVIDE -- it handles division-by-zero gracefully (returns BLANK):
pbi measure create "Margin %" \
  -e "DIVIDE(SUM(Sales[Amount]) - SUM(Sales[Cost]), SUM(Sales[Amount]))" \
  -t Sales --format-string "0.0%"

# Instead of: VAR current = [Total Sales] / VAR prev = [Sales PY] / RETURN DIVIDE(...)
# Reference measures directly in DIVIDE:
pbi measure create "YoY %" \
  -e "DIVIDE([Total Sales] - [PY Sales], [PY Sales])" \
  -t Sales --format-string "0.0%"

Validating Queries

pbi dax validate "EVALUATE Sales"
pbi dax validate --file query.dax

Cache Management

pbi dax clear-cache    # Clear the formula engine cache

Creating Measures with DAX

# Simple aggregation
pbi measure create "Total Sales" -e "SUM(Sales[Amount])" -t Sales

# Time intelligence
pbi measure create "YTD Sales" -e "TOTALYTD(SUM(Sales[Amount]), Calendar[Date])" -t Sales

# Previous year comparison
pbi measure create "PY Sales" -e "CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date]))" -t Sales

# Year-over-year change
pbi measure create "YoY %" -e "DIVIDE([Total Sales] - [PY Sales], [PY Sales])" -t Sales --format-string "0.0%"

Common DAX Patterns

Explore Model Data

# List all tables
pbi dax execute "EVALUATE INFO.TABLES()"

# List columns in a table
pbi dax execute "EVALUATE INFO.COLUMNS()"

# Preview table data
pbi dax execute "EVALUATE TOPN(10, Sales)"

# Count rows
pbi dax execute "EVALUATE ROW(\"Count\", COUNTROWS(Sales))"

Aggregations

# Basic sum
pbi dax execute "EVALUATE ROW(\"Total\", SUM(Sales[Amount]))"

# Group by with aggregation
pbi dax execute "EVALUATE SUMMARIZECOLUMNS(Products[Category], \"Total\", SUM(Sales[Amount]))"

# Multiple aggregations
pbi dax execute "
EVALUATE
SUMMARIZECOLUMNS(
    Products[Category],
    \"Total Sales\", SUM(Sales[Amount]),
    \"Avg Price\", AVERAGE(Sales[UnitPrice]),
    \"Count\", COUNTROWS(Sales)
)
"

Filtering

# CALCULATE with filter
pbi dax execute "
EVALUATE
ROW(\"Online Sales\", CALCULATE(SUM(Sales[Amount]), Sales[Channel] = \"Online\"))
"

# FILTER with complex condition
pbi dax execute "
EVALUATE
FILTER(
    SUMMARIZECOLUMNS(Products[Name], \"Total\", SUM(Sales[Amount])),
    [Total] > 1000
)
"

Time Intelligence

# Year-to-date
pbi dax execute "
EVALUATE
ROW(\"YTD\", TOTALYTD(SUM(Sales[Amount]), Calendar[Date]))
"

# Rolling 12 months
pbi dax execute "
EVALUATE
ROW(\"R12\", CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -12, MONTH)
))
"

Ranking

# Top products by sales
pbi dax execute "
EVALUATE
TOPN(
    10,
    ADDCOLUMNS(
        VALUES(Products[Name]),
        \"Total\", CALCULATE(SUM(Sales[Amount]))
    ),
    [Total], DESC
)
"

Performance Tips

• Use --max-rows to limit result sets during development
• Run pbi dax clear-cache before benchmarking
• Prefer SUMMARIZECOLUMNS over SUMMARIZE for grouping
• Use CALCULATE with simple filters instead of nested FILTER
• Avoid iterators (SUMX, FILTER) on large tables when aggregations suffice

Gotchas

• VAR/RETURN newlines die in -e: Shell collapses newlines into spaces, and the DAX parser needs them as statement separators. VAR x = ... VAR y = ... RETURN ... on one line throws a "syntax error" with no useful pointer. Pipe from stdin or use --file.
• CALCULATE(SUM(x), ALL(table)) vs ALLEXCEPT vs ALLSELECTED: All three return plausible numbers — only one is correct. ALL clears everything on that table; ALLEXCEPT keeps only listed columns; ALLSELECTED respects outer filters. The wrong choice yields silently wrong totals.
• SUMMARIZECOLUMNS drops rows where every measure is BLANK: A "missing category" in your output is often this filter, not missing data. Wrap measures in COALESCE(measure, 0) if the row must appear.
• DIVIDE([a], [b]) returns BLANK on divide-by-zero, not 0: Blanks propagate into downstream charts as gaps rather than zeros. Pass the third arg explicitly: DIVIDE([a], [b], 0) when zero behavior is intended.
• pbi dax clear-cache only clears the formula engine cache: Storage engine cache survives. For an honest cold-query benchmark, also restart Desktop or re-attach the connection — repeat runs otherwise look unrealistically fast.
• INFO.TABLES() returns hidden tables too: Calculation groups, auto-date hierarchies, and translation tables show up alongside user tables. Filter [IsHidden] = FALSE to get just what a report author sees.