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