Data Model & DAX¶
PREREQUISITE: Tables must be added to Data Model first!
The Data Model (Power Pivot) only contains tables that were explicitly added. You CANNOT create DAX measures on tables that aren't in the Data Model.
MSOLAP Prerequisite (for evaluate/execute-dmv)¶
The evaluate and execute-dmv actions require Microsoft Analysis Services OLE DB Provider (MSOLAP).
If you see "Class not registered" (0x80040154) error, install one of: 1. Power BI Desktop (recommended - includes MSOLAP): https://powerbi.microsoft.com/desktop 2. Microsoft OLE DB Driver for Analysis Services: https://learn.microsoft.com/analysis-services/client-libraries 3. SQL Server Analysis Services client tools
After installation, restart Excel and try again.
CRITICAL: Data Model Sync (Worksheet Tables)¶
Worksheet tables and Data Model tables are SEPARATE copies!
When you append/modify a worksheet table, the Data Model does NOT auto-update. You MUST explicitly refresh the Data Model to sync changes.
## WRONG: Data still shows old values
table(append, tableName="Sales", csvData="...") # Worksheet updated
datamodel(evaluate, daxQuery="...") # Returns OLD values!
## CORRECT: Refresh Data Model after worksheet changes
table(append, tableName="Sales", csvData="...") # Worksheet updated
datamodel(refresh) # Sync to Data Model
datamodel(evaluate, daxQuery="...") # Returns NEW values!
When refresh is automatic: - powerquery(refresh) refreshes BOTH Power Query AND Data Model - Tables loaded via Power Query auto-sync on Power Query refresh
When refresh is REQUIRED: - After table(append) to worksheet table - After range(set-values) that modifies table data - After any manual/direct worksheet edits
Excel Power Pivot Limitations (vs SSAS/Power BI)¶
| Feature | Power BI/SSAS | Excel Power Pivot | Workaround |
|---|---|---|---|
| Calculated Tables | DAX: MyTable = FILTER(...) | NOT SUPPORTED | Use Power Query to create the table |
| Calculated Columns | DAX: Table[Col] = ... | Read-only names/types; no COM formula or mutation API | Use Power Query or DAX measures |
| Measures | Full support | Full support | - |
| Relationships | Full support | Full support | - |
Key Insight: ModelTableColumn exposes only Name, DataType, and Parent in Excel's PIA. list-columns and read-table return both the raw XlParameterDataType value and its readable name. Excel's COM API cannot read a calculated-column formula or create, modify, or delete calculated columns. If you need computed columns: 1. Preferred: Add the column in Power Query (computed at refresh time) 2. Alternative: Use a DAX measure instead (computed at query time)
How to add tables to the Data Model:
| Source | Method |
|---|---|
| Worksheet Excel Table | table with add-to-data-model action |
| External file (CSV, etc.) | powerquery with loadDestination='data-model' |
| Database/web source | powerquery with loadDestination='data-model' |
DAX Formatting:
DAX formulas are preserved exactly by default on WRITE operations (create-measure, update-measure), subject to Excel locale separator translation. Set formatDax=true only with explicit user consent; it sends DAX to daxformatter.com. Remote formatting adds ~100-500ms network latency per write operation. If formatting fails (network issues, API errors), the original DAX is saved unchanged - operations never fail due to formatting.
Action disambiguation:
- list-tables: List all tables currently in the Data Model
- list-measures: List all DAX measures (returns raw DAX from Excel)
- create-measure: Create a new DAX measure (DAX preserved by default;
formatDax=trueopts into remote formatting) - update-measure: Modify existing measure's formula/format/description (DAX preserved by default;
formatDax=trueopts into remote formatting) - delete-measure: Remove a measure
- delete-table: Remove table AND ALL its measures (DESTRUCTIVE!)
- read-info: Get Data Model metadata (culture, compatibility level)
- read-connection: Get the embedded model connection name/type, ModelConnection command metadata, and connected table names
- read-table: Get table columns and source connection metadata
- refresh: Refresh all Data Model data from sources
- evaluate: Execute DAX EVALUATE queries and return tabular results (read-only, no side effects)
- execute-dmv: Execute DMV queries for metadata discovery (SELECT * FROM $SYSTEM.*)
evaluate action:
Execute any DAX EVALUATE query against the Data Model and return results as JSON. Useful for ad-hoc analysis, testing DAX expressions, or extracting aggregated data.
// Examples of valid EVALUATE queries:
EVALUATE 'SalesTable' // Return entire table
EVALUATE TOPN(10, 'Sales', 'Sales'[Amount], DESC) // Top 10 by amount
EVALUATE SUMMARIZE('Sales', 'Sales'[Region], "Total", SUM('Sales'[Amount])) // Aggregation
EVALUATE FILTER('Products', 'Products'[Category] = "Electronics") // Filtered
EVALUATE ROW("TotalRevenue", SUM('Sales'[Amount])) // Single row result
execute-dmv action (DMV = Dynamic Management Views):
Execute SQL-like DMV queries to discover Data Model metadata. DMVs are schema rowsets that expose Analysis Services internal information.
SYNTAX: SELECT * FROM $SYSTEM.<SchemaRowset>
IMPORTANT LIMITATIONS (Excel's embedded Analysis Services): - ONLY SELECT * works - specific column selection (SELECT col1, col2) fails - Some TMSCHEMA views return empty results despite Data Model having data - Excel's embedded AS has limited support compared to full SQL Server Analysis Services
Working DMV queries (verified in Excel):
| DMV Query | Returns |
|---|---|
SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES | All DAX measures with formulas |
SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS | All relationships between tables |
SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY | Calculation dependencies (useful for impact analysis) |
SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS | Database/catalog metadata |
SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS | List all available DMVs |
DMV queries that execute but may return empty in Excel:
| DMV Query | Notes |
|---|---|
SELECT * FROM $SYSTEM.TMSCHEMA_TABLES | May return 0 rows in Excel's embedded AS |
SELECT * FROM $SYSTEM.TMSCHEMA_COLUMNS | May return 0 rows in Excel's embedded AS |
SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONS | May return 0 rows in Excel's embedded AS |
Full list of TMSCHEMA DMVs (from MS-SSAS-T protocol):
| Category | DMVs |
|---|---|
| Model Structure | TMSCHEMA_MODEL, TMSCHEMA_TABLES, TMSCHEMA_COLUMNS, TMSCHEMA_HIERARCHIES, TMSCHEMA_LEVELS |
| Measures/KPIs | TMSCHEMA_MEASURES, TMSCHEMA_KPIS, TMSCHEMA_FORMAT_STRING_DEFINITIONS |
| Relationships | TMSCHEMA_RELATIONSHIPS |
| Security | TMSCHEMA_ROLES, TMSCHEMA_ROLE_MEMBERSHIPS, TMSCHEMA_TABLE_PERMISSIONS, TMSCHEMA_COLUMN_PERMISSIONS |
| Partitions | TMSCHEMA_PARTITIONS, TMSCHEMA_DATA_SOURCES |
| Metadata | TMSCHEMA_ANNOTATIONS, TMSCHEMA_EXTENDED_PROPERTIES, TMSCHEMA_CULTURES, TMSCHEMA_OBJECT_TRANSLATIONS |
| Perspectives | TMSCHEMA_PERSPECTIVES, TMSCHEMA_PERSPECTIVE_TABLES, TMSCHEMA_PERSPECTIVE_COLUMNS, TMSCHEMA_PERSPECTIVE_MEASURES |
| Calculations | TMSCHEMA_CALCULATION_GROUPS, TMSCHEMA_CALCULATION_ITEMS, TMSCHEMA_EXPRESSIONS |
DISCOVER DMVs (server/analysis metadata):
| DMV | Description |
|---|---|
| DISCOVER_CALC_DEPENDENCY | Dependencies between objects (great for impact analysis) |
| DISCOVER_SCHEMA_ROWSETS | List all available schema rowsets |
| DISCOVER_PROPERTIES | Server properties |
| DISCOVER_KEYWORDS | Reserved keywords |
| DISCOVER_LITERALS | Supported literals |
Example use cases:
-- Find all measures and their DAX formulas
SELECT * FROM $SYSTEM.TMSCHEMA_MEASURES
-- Discover what objects a measure depends on
SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY
-- List all relationships
SELECT * FROM $SYSTEM.TMSCHEMA_RELATIONSHIPS
-- Get catalog information
SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS
Reference: Microsoft DMV Documentation
DAX measure creation:
- tableName: Which table the measure belongs to (for organization)
- measureName: Display name for the measure
- daxFormula: DAX expression (e.g., "SUM(Sales[Revenue])")
- formatString: Optional number format (#,##0.00, 0%, $#,##0, etc.)
Common DAX patterns:
// Sum
SUM(TableName[ColumnName])
// Average
AVERAGE(TableName[ColumnName])
// Count rows
COUNTROWS(TableName)
// Calculated ratio
DIVIDE(SUM(Sales[Revenue]), SUM(Sales[Units]), 0)
Displaying Data Model Data - Choose the Right Output¶
| Goal | Best Tool | Why |
|---|---|---|
| Flat query results | table create-from-dax | Clean tabular display, no PivotTable UI |
| Static reports/snapshots | table create-from-dax | DAX does aggregation, table just displays |
| Data for formulas | table create-from-dax | Use structured references like =SUM(Sales[Amount]) |
| Interactive drill-down | pivottable | User can regroup, filter, expand/collapse |
| Cross-tabulation (rows × columns) | pivottable | Matrix layout with row/column fields |
Rule: Prefer table create-from-dax for displaying query results. Use pivottable only when the user needs interactive analysis capabilities.
Charting Data Model Data - Use PivotChart Directly¶
WRONG: Create PivotTable → Create separate Chart from PivotTable data RIGHT: Use chart create-from-pivottable to create a PivotChart directly
A PivotChart is a single object connected to the Data Model. Creating a PivotTable + separate chart is unnecessary extra work and creates two objects to maintain.
Star Schema Architecture¶
Why use DAX over Power Query for calculations?
- DAX recalculates on refresh without re-running Power Query
- Useful when lookup/rate tables change frequently
Common mistakes:
- Creating measures before adding source table to Data Model → Error
- Using worksheet table names instead of Data Model table names
- Forgetting that delete-table removes ALL measures on that table
- Not specifying tableName when creating measures (required for organization)
Server-specific quirks:
- 2-minute auto-timeout on Data Model operations
- Table names in Data Model may differ from worksheet (check list-tables)
- Refresh is synchronous and can target the whole model or one table; Excel exposes no live model/table
Refreshingstatus through COM ModelTable.LastRefreshis missing from the Excel PIA and unavailable at runtime in supported Excel builds, so no reliable model-table refresh timestamp can be reported- Measure names must be unique across entire Data Model (not per-table)