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%"