[Part 3] The Semantic Model Is Your Foundation [Fabric App]

At the end of this part you have a published demo model with a date table and eleven KPI measures, written by AI from exact „coordinates“ and checked against known numbers.

Time: about 15–20 minutes.

Credits: none if you use any AI chat for the measures; I did this step with Claude.

This is where we BI people have home advantage. The app in Parts 4 to 7 never recalculates a single number: it asks the model via DAX. So the better the model, the better the app. And the clearer the names, the better the AI’s code later.


The demo data

Three fictional CSV files, a small star schema with English names:

Artikelinhalte

Two patterns are built in, so the forecast has something to show: IT Accessories grows in 2026, and Office Furniture in East is weaker. The data ends in September 2026, so October to December are the months we forecast.

🔗https://github.com/K-Borchert/Fabric_Apps


Build the model

You do (Power BI Desktop, about 7 minutes):

  1. Load the three CSVs in Import mode. Relate them on RegionID and ProductGroupID.
  2. Modeling → New table, and create a date table that also covers the forecast months:
Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Month No", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year Month Key", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Month Start", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
)
  1. Mark it as date table, sort Month by Year Month Key, and relate Date[Date] to fact_sales_actual[Month].

Measures: give the AI coordinates, not descriptions

This is the trick I’m proudest of in this series. Instead of describing my model in words, I give the AI its exact definition. Power BI Desktop has it ready for us in the TMDL view.

You do:

  1. Home → Enter data. Name the table KPI, leave the empty column, click Load.
  2. Open the TMDL view (left sidebar) and drag the KPI table into the editor. Power BI writes a createOrReplace script with the table’s exact definition.
  3. Copy that script into the prompt below.
  4. Paste the AI’s answer back into the TMDL view and click Apply. Use tabs, not spaces.

Prompt for your AI:

I am building a Power BI semantic model for a sales forecast app. Add measures to my measure table KPI and return the complete TMDL script, ready to paste into the TMDL view of Power BI Desktop.

Model coordinates:
- Fact table fact_sales_actual: Month (date, first day of month), RegionID, ProductGroupID, Sales_EUR, Quantity
- Dimensions dim_region (RegionID, Region, SalesOffice) and dim_product_group (ProductGroupID, ProductGroup, AvgPrice_EUR)
- Date table 'Date' with Date, Year, Month, Month Start; marked as date table, related to fact_sales_actual[Month]

Measures needed: Sales Actual, Quantity, Avg Price, Sales PY, Variance PY, Variance PY %, Sales YTD, Last Actual Month, Sales PY YTD (only prior-year months up to the last actual month), Growth YTD %, and Forecast Suggestion (same month last year × (1 + Growth YTD %), only for months after the last actual month).

Requirements: use display folders Actuals, Prior Year and Forecast; EUR format strings without decimals; percentages with one decimal; hide the placeholder column; keep my existing lineageTag, column and partition; indent with tabs.

Here is my current TMDL script of the KPI table:
<paste the createOrReplace script here>

The core of what the AI should return, in DAX:

Sales Actual = SUM ( fact_sales_actual[Sales_EUR] )
Sales PY = CALCULATE ( [Sales Actual], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YTD = CALCULATE ( [Sales Actual], DATESYTD ( 'Date'[Date] ) )
Last Actual Month = CALCULATE ( MAX ( fact_sales_actual[Month] ), REMOVEFILTERS () )
Sales PY YTD =
VAR LastActualPY = EDATE ( EOMONTH ( [Last Actual Month], 0 ), -12 )
RETURN CALCULATE ( [Sales Actual], SAMEPERIODLASTYEAR ( DATESYTD ( 'Date'[Date] ) ), 'Date'[Date] <= LastActualPY )
Growth YTD % = DIVIDE ( [Sales YTD] - [Sales PY YTD], [Sales PY YTD] )
Forecast Suggestion =
VAR LastActual = EOMONTH ( [Last Actual Month], 0 )
RETURN SUMX ( FILTER ( VALUES ( 'Date'[Month Start] ), 'Date'[Month Start] > LastActual ),
    CALCULATE ( [Sales PY] * ( 1 + [Growth YTD %] ) ) )

Two design points worth knowing. Sales PY YTD only counts prior-year months up to the last actual month; otherwise growth would look negative because a full prior year is compared with nine months. And Forecast Suggestion is calculated, not hardcoded: when October actuals arrive, the forecast moves on by itself.

Stumbling block: measures pasted via TMDL fail. Usually spaces instead of tabs, or the ‚Date‘ table doesn’t exist yet. Create the date table first.


Checkpoint: compare against known numbers

I calculated these from the CSV, independently of Power BI. If your card visuals show the same, the model is right.

Artikelinhalte

Publish

You do: publish to a workspace on Fabric capacity or trial. I used the same workspace for model and app, which keeps permissions simple. Write down the model’s URL; Part 4 needs two IDs from it.

⚠️ One more thing to remember for later: every app user needs Build and Read on this model, because the app queries it with their identity.

Commit point: nothing to commit in Git; the model lives in Power BI. Save your .pbip or .pbix


Fabric Apps is in preview; menus and commands may change. Written October 2026. Co-authored with Claude as co-learner. Previous: Part 2 · Next: Part 4. #HumanAICoProgramming

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert