There is an old Italian saying "If it's not formatted, it is not DAX" 😁
When you get the list of measures from SemPy, it's not formatted and is hard to read and understand. Thankfully, the SQLBI team has made the DAX parser and the formatter available via an API. I wrote a quick function to return the formatted DAX expression of a measure. You can either pass a DAX expression or the FabricDataFrame returned by fabric.list_measures()
Pre Requisites
Microsoft Fabric
Dataset in a Premium (F, P, PPU) workspace in Fabric tenant
semantic-link and beautifulsoup4 installed
Steps:
Install Semantic Link and BeautifulSoup
!pip install beautifulsoup4 semantic-link --q
Get a list of measures for a dataset
import sempy.fabric as fabric
from sempy.fabric import FabricDataFrame
import pandas as pd
ws = "Sales Workspace"
ds = "Sales & Returns"
measures_df = fabric.list_measures(workspace=ws, dataset=ds)
Function to call the DAX Formatter API
import requests
from bs4 import BeautifulSoup
def format_dax(dax_expression=None, measure_df=None, measure_name=None, line='short', region='US'):
"""
Author: Sandeep Pawar | fabric.guru
You must specify either the DAX for the measure or the measure_df + measure_name
But not both.
Refer to https://www.daxformatter.com/ for DAX Formatter details and options
"""
if (dax_expression is None and measure_df is None) or (dax_expression is not None and (measure_df is not None and measure_name is not None)):
return "Error: Provide either a DAX expression or a combination of measure_df and measure_name, but not both."
if dax_expression is not None:
dax_to_format = dax_expression
else:
if measure_df is not None and measure_name is not None:
measure_row = measure_df[measure_df['Measure Name'] == measure_name].iloc[0]
dax_to_format = f"{measure_row['Measure Name']}={measure_row['Measure Expression']}"
else:
return "Error: Provide either a DAX expression or a combination of measure_df and measure_name."
url = "https://www.daxformatter.com"
payload = {
'r': region,
'fx': dax_to_format,
'embed': '1',
's': 'auto',
'l': line
}
try:
response = requests.post(url, data=payload)
response.raise_for_status()
soup = BeautifulSoup(response.text, 'html.parser')
formatted_dax_div = soup.find('div', {'class': 'formatted'})
for br in formatted_dax_div.find_all("br"):
br.replace_with("\n")
formatted_dax_text = formatted_dax_div.get_text(' ').replace('\xa0', ' ')
return print(formatted_dax_text)
except Exception as e:
return f"Error: {e}"
Format a specific measure from the FabricDataFrame:
format_dax(dax_expression=None, measure_df = measures_df, measure_name = "WIF Units Returned_2")
To format any measure that's not in the above dataframe, pass it to the dax_expression instead. DAX here should always start with the name of the measure.
measure_expression = """Sales Amount = VAR RoundedNetPrices =
ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Net Price] ),
"@Rounded Net Price", ROUND ( Sales[Net Price], 1 ),
"@Sum Of Quantity", CALCULATE ( SUM ( Sales[Quantity] ) )
)
VAR Result = SUMX ( RoundedNetPrices, [@Rounded Net Price] * [@Sum Of Quantity] )
RETURN Result"""
format_dax(dax_expression=measure_expression, measure_df = None, measure_name = None)
💡
The default is short line but you can change it by line="long"
format_dax(dax_expression=measure_expression, measure_df = None, measure_name = None, line='long')
This API is generously made available by the SQLBI team so be respectful of the limits, terms and conditions. Thank you SQLBI.