Power Bi Modeling

Power BI Modeling Skill

Use pbi-cli to manage semantic model structure. Requires pipx install pbi-cli-tool, pbi-cli skills install, and pbi connect.

Prerequisites

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

Tables

pbi table list                                    # List all tables
pbi table get Sales                               # Get table details
pbi table create Sales --mode Import              # Create table
pbi table delete OldTable                         # Delete table
pbi table rename OldName NewName                  # Rename table
pbi table refresh Sales --type Full               # Refresh table data
pbi table schema Sales                            # Get table schema
pbi table mark-date Calendar --date-column Date   # Mark as date table

Columns

pbi column list --table Sales                                       # List columns
pbi column get Amount --table Sales                                 # Get column details
pbi column create Revenue --table Sales --data-type double --source-column Revenue  # Data column
pbi column create Profit --table Sales --expression "[Revenue]-[Cost]"              # Calculated
pbi column delete OldCol --table Sales                              # Delete column
pbi column rename OldName NewName --table Sales                     # Rename column

Measures

pbi measure list                                                    # List all measures
pbi measure list --table Sales                                      # Filter by table
pbi measure get "Total Revenue" --table Sales                       # Get details
pbi measure create "Total Revenue" -e "SUM(Sales[Revenue])" -t Sales                        # Basic
pbi measure create "Revenue $" -e "SUM(Sales[Revenue])" -t Sales --format-string "\$#,##0"  # Formatted
pbi measure create "KPI" -e "..." -t Sales --folder "Key Measures" --description "Main KPI" # With metadata
pbi measure update "Total Revenue" -t Sales -e "SUMX(Sales, Sales[Qty]*Sales[Price])"       # Update expression
pbi measure delete "Old Measure" -t Sales                           # Delete
pbi measure rename "Old" "New" -t Sales                             # Rename
pbi measure move "Revenue" -t Sales --to-table Finance              # Move to another table

Multi-line DAX in measure expressions: The -e flag passes DAX as a shell argument, which collapses newlines. For simple expressions like SUM(Sales[Amount]) or DIVIDE([A] - [B], [B]) this works fine. For complex expressions using VAR/RETURN, pipe from stdin instead:

echo 'VAR TotalSales = SUM(Sales[Amount])
VAR TotalCost = SUM(Sales[Cost])
RETURN TotalSales - TotalCost' | pbi measure create "Profit" -e - -t Sales

See the power-bi-dax skill for the full explanation and more workarounds.

Relationships

pbi relationship list                              # List all relationships
pbi relationship get RelName                       # Get details
pbi relationship create \
  --from-table Sales --from-column ProductKey \
  --to-table Products --to-column ProductKey       # Create relationship
pbi relationship delete RelName                    # Delete
pbi relationship find --table Sales                # Find relationships for a table
pbi relationship activate RelName                  # Activate
pbi relationship deactivate RelName                # Deactivate

Hierarchies

pbi hierarchy list --table Date                    # List hierarchies
pbi hierarchy get "Calendar" --table Date          # Get details
pbi hierarchy create "Calendar" --table Date       # Create
pbi hierarchy delete "Calendar" --table Date       # Delete

Calculation Groups

pbi calc-group list                                # List calculation groups
pbi calc-group create "Time Intelligence" --description "Time calcs"  # Create group
pbi calc-group items "Time Intelligence"           # List items
pbi calc-group create-item "YTD" \
  --group "Time Intelligence" \
  --expression "CALCULATE(SELECTEDMEASURE(), DATESYTD(Calendar[Date]))"  # Add item
pbi calc-group delete "Time Intelligence"          # Delete group

Creating a Date/Calendar Table

Date tables are essential for time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, etc.).

# Create a calculated date table with DAX (covers full calendar years)
pbi table create Calendar \
  --dax-expression "ADDCOLUMNS(CALENDAR(DATE(2023,1,1), DATE(2024,12,31)), \"Year\", YEAR([Date]), \"MonthNumber\", MONTH([Date]), \"MonthName\", FORMAT([Date], \"MMMM\"), \"Quarter\", \"Q\" & FORMAT([Date], \"Q\"))"

# Mark it as a date table (required for time intelligence)
pbi table mark-date Calendar --date-column Date

# Verify it's recognized as a date table
pbi calendar list

Workflow: Create a Star Schema

# 1. Create fact table
pbi table create Sales --mode Import

# 2. Create dimension tables
pbi table create Products --mode Import
pbi table create Calendar --mode Import

# 3. Create relationships
pbi relationship create --from-table Sales --from-column ProductKey --to-table Products --to-column ProductKey
pbi relationship create --from-table Sales --from-column DateKey --to-table Calendar --to-column DateKey

# 4. Mark date table
pbi table mark-date Calendar --date-column Date

# 5. Add measures
pbi measure create "Total Revenue" -e "SUM(Sales[Revenue])" -t Sales --format-string "\$#,##0"
pbi measure create "Total Qty" -e "SUM(Sales[Quantity])" -t Sales --format-string "#,##0"
pbi measure create "Avg Price" -e "AVERAGE(Sales[UnitPrice])" -t Sales --format-string "\$#,##0.00"

# 6. Verify
pbi table list
pbi measure list
pbi relationship list

Best Practices

• Use format strings for currency ($#,##0), percentage (0.0%), and integer (#,##0) measures
• Organize measures into display folders by business domain
• Always mark calendar tables with mark-date for time intelligence
• Use --json flag when scripting: pbi --json measure list
• Export TMDL for version control: pbi database export-tmdl ./model/

Gotchas

• mark-date requires a contiguous date column with no gaps: A calendar table missing a single day (leap year, manual filter) accepts mark-date without error but silently fails time-intelligence functions like SAMEPERIODLASTYEAR — they return BLANK with no diagnostic.
• relationship create defaults to bidirectional in some pbi-cli versions: Always inspect with pbi --json relationship get RelName immediately after creation. Bidirectional joins between a fact and a small dimension table will silently destroy filter context propagation in unrelated measures.
• Calculated tables created via --dax-expression do not refresh on data refresh: They re-evaluate at process-recalc time only. Edits to underlying tables won't propagate until a Full or Calculate refresh — easy to miss in a partial-refresh pipeline.
• measure move --to-table updates the home table but NOT references in DAX: Existing measures referencing Sales[Margin] still work because measures are model-scoped, but the visual report.json may keep the old table reference and break when the source table is later renamed.
• format-string with literal $ needs shell escaping: --format-string "$#,##0" becomes #,##0 after shell variable expansion. Use --format-string "\$#,##0" or wrap in single quotes — the resulting measure looks "wrong" with no error.
• Calculation group creation order matters for sort: The first item created becomes ordinal 0 and is the default. Add a "No Time Calc" placeholder first or measures will silently apply YTD when nothing is selected.