How to create Measure Tables in Microsoft Fabric

How to create Measure Tables in Microsoft Fabric

Creating Measure Tables in Microsoft Fabric Semantic Models

Measure Tables have long been used to store Measures in Power BI Semantic Models.

However, in Microsoft Fabric, where most of the data modeling takes place in the browser, at least if we wish to reap the benefits of the DirectLake storage mode, the process of creating Measure Tables are slightly different.

Step 1: Creating an empty table

First you need to create an empty table. Or rather, a table with one column in it (a table can’t have zero columns in Fabric), but you should not add any data to this table.

If the Data Lakehouse is your weapon of choice, you may use a Notebook to do so with Python:

data = [(1, 'MeasureTable')]
columns = ['ID', 'Col1']

measure_df = spark.createDataFrame(data, columns)
measure_df.show()

spark.sql("DROP TABLE IF EXISTS MeasureTable")
measure_df.write.format("delta").saveAsTable('MeasureTable')

If you are using the Lakehouse SQL Endpoint you can create an empty view, or if you are using Data Warehouse, create a table with SQL:

Step 2: Pulling the table into your Semantic Model

The next step is to create a Semantic Model on top of your Warehouse or Lakehouse:

And subsequently, add a couple of measures, before finally hiding the only column present in the table, to convert it into a Measure Table:

And there you have it! It is that simple to create centralized measure tables in your Microsoft Fabric semantic models. Go build!