Data Model & DAX¶

Worksheet Tables and Data Model tables are separate. Add an existing Excel Table to the model, or load a Power Query to data-model/both, before creating relationships or measures. Use model table listings to discover exact names. Current commands and inputs come from CLI help or MCP tool descriptions.

Creating a measure¶

The following assumes an existing worksheet Table named Sales with an Amount column and a captured session. Check each result before continuing.

table(action: 'add-to-data-model', workbook_session_id: sessionId, table_name: 'Sales')
datamodel(action: 'create-measure', workbook_session_id: sessionId, table_name: 'Sales', measure_name: 'Total Sales', dax_formula: 'SUM(Sales[Amount])')
datamodel_read(action: 'evaluate', workbook_session_id: sessionId, dax_query: 'EVALUATE ROW("Total", [Total Sales])')

excelcli -q table add-to-data-model --session $sessionId --table-name Sales
excelcli -q datamodel create-measure --session $sessionId --table-name Sales --measure-name 'Total Sales' --dax-formula 'SUM(Sales[Amount])'
excelcli -q datamodel evaluate --session $sessionId --dax-query 'EVALUATE ROW("Total", [Total Sales])'

Measure names are unique across the model, not just their home table. Update an existing measure rather than trying to create it again. Choose its display format from the result's meaning; percentages and decimals are not interchangeable. Read back the actual definition and result, especially after failure, because a write can leave partial changes.

Supply native DAX with comma argument separators and decimal points. Remote formatting is optional, sends code to an external service, and requires consent.

When Windows uses a comma as the decimal mark, Excel misreads a comma that touches a number in a measure formula, for example turning IF(..., 1.5, 0) into an invalid formula or DATEADD(..., -1, MONTH) into -1.. Create and update then add one space on each side of such commas (IF(..., 1.5 , 0), DATEADD(..., -1 , MONTH)). Text, quoted names, column references, and comments are left alone. The DAX meaning is unchanged, and the result message says spaces were added. Do not change regional settings or enable remote formatting as a workaround. Measure readback returns the DAX that the model stores, with decimal points, including after the workbook is reopened.

No machine or Excel regional settings are changed. Measure writes store a definition; Excel may defer validation of an unknown function until evaluation. A successful write is not proof of a successful calculation. Evaluate the measure in the intended context and inspect its value.

Refresh is not calculation¶

After worksheet Table edits or appends, refresh the model before querying it. Power Query refresh updates its model load. Refresh dependent PivotTables after their source is current.

DAX measures are evaluated at query time in the current filter context; they do not reload source data. A changed external rate/lookup table still needs a source refresh. Put stored transformations and computed columns in Power Query; use measures for calculations over current model data.

Model refresh defaults to a 30-minute wait. Refresh and querying have different limits; use their current help rather than applying one blanket timeout to every operation. An expired wait preserves the open workbook and reveals its Excel window when possible; it does not prove that refresh stopped or failed. Inspect session status before further work. Excel exposes no reliable live model-refresh status or per-table refresh timestamp here. Refresh failures preserve Excel's error details rather than assuming that model-level refresh is unsupported. Check the reported source or engine error before choosing recovery; refreshing individual tables is not a universal fix.

Model metadata reads fail when a required property cannot be read. A reported row count of zero means Excel returned zero, not that its property getter failed. For list-columns and read-table, each column's isCalculated field is explicitly null (unknown). Excel's supported column API does not expose that flag. An unknown value does not mean the column is not calculated; an empty metadata query is not evidence of that either. Both MCP and CLI return the same field and value.

Relationships and unsupported features¶

Relationships connect the detail/many side to the unique lookup/one side with compatible column types. Only one relationship per table pair can be active; use DAX USERELATIONSHIP for an inactive one. List relationships before changing or deleting model tables. Deleting a model table also removes its measures and relationships.

Excel Power Pivot does not expose DAX calculated-table creation through these tools. Its COM column API exposes names and types, not calculated-column formula reading or mutation. Use Power Query computed columns or measures instead.

Querying and displaying results¶

DAX queries return analytical results; DMV queries inspect model metadata. Both require the Microsoft Analysis Services OLE DB provider (MSOLAP). If it is missing, report the prerequisite; see Microsoft's client libraries. Do not diagnose every Excel error as missing MSOLAP. Date columns returned by evaluate may appear as Excel date-serial numbers, not formatted date strings. Check the returned values and types before using them as display-ready dates.

Use DMV guidance for supported rowsets and Excel limitations. Some rowsets return no rows even when a model exists.

For per-row revenue, use SUMX(Sales, Sales[Quantity]*Sales[UnitPrice]), not a product of column totals. Use DIVIDE when a denominator may be zero.