T-SQL Tuesday 171 – Using SemPy to view DAX in Microsoft Fabric

Reading Time: 4 minutes

For this months T-SQL Tuesday contribution I want to cover how I ended up using SemPy to view DAX in Microsoft Fabric.

To be more precise, using the new(ish) SemPy library within a notebook to query the DAX used for a measure within a published Power BI report.

Before I go any further, I want to thank Brent Ozar for hosting another T-SQL Tuesday.

Even more so since he clearly stated that it does not need to be about T-SQL. Which means that this post is very different than my previous T-SQL Tuesday 136 post that Brent also hosted.

Anyway, this time round Brent invites us all to talk about the most recent task/issue we closed. You can read the original invitation by clicking on this link for the T-SQL Tuesday 171 invitation or on the image below.

The issue

Recently I have been helping others get up to speed with Microsoft Fabric. Which includes going through some Power BI topics.

One issue that came up was how to show them the DAX used for a measure within a Power BI report that had been published to Microsoft Fabric. To link working with measures in Power BI Desktop with working in Microsoft Fabric.

Typically, you do this by enabling the workspace setting to edit data models as below.

Once selected you can view the DAX used by opening the data model in Microsoft Fabric and selecting the measure.

However, this was not an option due to workspace permissions. So, I had to look for an alternative solution.

Using SemPy to view DAX in Microsoft Fabric

I figured out an alternative way to view the DAX used for a measure within Microsoft Fabric by using the new SemPy library. Which allows you to directly query the contents of a semantic model (dataset) from a notebook within Microsoft Fabric.

In order to do this, I created a notebook in the same Microsoft Fabric workspace and first ran the below command.

# Install SemPy
%pip install semantic-link

I first ran the below code to verify that it worked.

# import the module
import sempy.fabric as fabric

# set the semantic model
sm = "Sales Analysis - Work with model relationships"

fabric.list_measures(sm)

It returned the below results.

However, I wanted others to see the results for one measure in particular. Whilst at the same time reminding them about working with the select syntax.

So, I wrote the below code to convert the Pandas DataFrame that is returned to a Spark DataFrame. To allow me to work with the select syntax.

# import the module
import sempy.fabric as fabric

# set the semantic model
sm = "Sales Analysis - Work with model relationships"

# create a dataframe based on the list of measures
# note that by default it creates a pandas dataframe
fabricdf = fabric.list_measures(sm)

# convert to a spark dataframe so that can use select syntax 
sparkdf = spark.createDataFrame(fabricdf)

daxdf=sparkdf.select("Measure Name","Measure Expression").where(sparkdf["Measure Name"]=="Sales Shipped")

display(daxdf)

Which returned the below results.

As you can see, doing it this way shows a nicely formatted result.

In reality, this code can be written in various ways. For example, the below code returns the same results.

# import the module
import sempy.fabric as fabric

# set the semantic model
sm = "Sales Analysis - Work with model relationships"

# create to a spark dataframe based on the returned Pandas one so that can use select syntax 
sparkdf = spark.createDataFrame(fabric.list_measures(sm))

daxdf=sparkdf.select("Measure Name","Measure Expression").where(sparkdf["Measure Name"]=="Sales Shipped")

display(daxdf)

However, the code that I went with served its purpose and makes for a simple demonstration.

Final words

I hope my T-SQL Tuesday contribution this month makes for an interesting read.

Because I wanted to highlight the benefit of enabling the workspace setting to work with data models. Plus, show how you can work with the new SemPy library within a notebook.

Of course, if you have any comments or queries relating to this post feel free to reach out to me.