Formatting DAX Expression Returned By SemPy in Fabric

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" #Specify your Premium workspace name or ID
ds = "Sales & Returns" #Specify dataset name or ID

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:

#Here I want to format a measure called "WIF Units Returned_2"
#Note here dax_expression is None. You cannot use both at the same time.
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.

##Unformatted DAX
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"""

# pass unformatted DAX
format_dax(dax_expression=measure_expression, measure_df = None, measure_name = None)

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.