power-bi-modeling

What this skill does

# 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

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

## Tables

```bash
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

```bash
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

```bash
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:

```bash
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

```bash
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

```bash
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

```bash
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.).

```bash
# 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

```bash
# 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.