Last year I shared a blog post on formatting DAX expressions using DAX Formatter API (thanks to SQL BI). I extend the same idea in this blog to format all and save the formatted DAX in all measures in a semantic model using semantic link.

Get A List of Measures

import sempy.fabric as fabric

ws = "Sales Workspace" #workspace name
ds = "FormatDaxTest"  #semantic model name

measures_df = fabric.list_measures(workspace=ws, dataset=ds)
measures_df

Optional: Backup The Measures

Before you modify the measures, I suggest backing it up just as a precaution.

#Mount a lakehouse and provide file api path as shown below
def backup_measures(workspace, dataset, file_api_path):
    return fabric.list_measures(workspace=workspace, dataset=dataset).to_csv(file_api_path)

backup_measures(workspace=ws, dataset=ds, file_api_path="/lakehouse/default/Files/measures.csv")

Format The DAX

Use the DAX Formatter API responsibly. Refer to the official documentation for API details.

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   |   06-13-2024
    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]
            if measure_row.empty:
                return f"Error: Measure '{measure_name}' not found in DataFrame."
            dax_to_format = measure_row.iloc[0]['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'})

        if formatted_dax_div is None:
            return "Error: No formatted DAX found on the page."

        for br in formatted_dax_div.find_all("br"):
            br.replace_with("\n")

        formatted_dax_text = formatted_dax_div.get_text(' ').replace('\xa0', ' ')

        return formatted_dax_text
    except Exception as e:
        return f"Error: {e}"

#Example usage
# format_dax(dax_expression= "SUM (Sales[SalesAmount])", measure_df=None, measure_name=None)

Format And Save Formatted DAX

import Microsoft.AnalysisServices.Tabular as tom

def save_formatted_dax_measures(workspace, dataset):

    """
    Sandeep Pawar  |   fabric.guru   | 06-13-2024
    Format and save all DAX measures in a semantic model. 
    Be sure to back up the measures before modifying the model.
    Once saved, the model cannnot be downloaded from service.

    """
    tom_server = fabric.create_tom_server(False, workspace)
    tom_database = tom_server.Databases.GetByName(dataset)
    model = tom_database.Model  
    for table in model.Tables:
        for measure in table.Measures:
            print("Formatting Measure: ", measure.Name)
            formatted = str(format_dax(dax_expression=measure.Expression, measure_df = None, measure_name = None))
            measure.Expression = formatted
    model.SaveChanges()  
    fabric.refresh_tom_cache(workspace=workspace)

save_formatted_dax_measures(workspace=ws, dataset=ds)

Bonus : Add DAX To The Measure Description

To add the measure definition to the measure description so it's visible to report authors in live mode scenarios, use following:

import Microsoft.AnalysisServices.Tabular as tom

def save_formatted_dax_measures(workspace, dataset):

    """
    Sandeep Pawar  |   fabric.guru   | 06-13-2024
    Format and save all DAX measures in a semantic model. 
    Adds DAX to measure description.
    Be sure to back up the measures before modifying the model.
    Once saved, the model cannnot be downloaded from service.

    """
    tom_server = fabric.create_tom_server(False, workspace)
    tom_database = tom_server.Databases.GetByName(dataset)
    model = tom_database.Model  
    for table in model.Tables:
        for measure in table.Measures:
            print("Formatting Measure: ", measure.Name)
            formatted = str(format_dax(dax_expression=measure.Expression, measure_df = None, measure_name = None))
            measure.Expression = formatted
            measure.Description = formatted
    model.SaveChanges()  
    fabric.refresh_tom_cache(workspace=workspace)

save_formatted_dax_measures(workspace=ws, dataset=ds)

Example

Here are before and after:

This script only formats the measures. You can easily modify it to include calculated columns and tables. You can also loop over all models to format DAX in all models in your tenant or use it in a pipeline to make sure the DAX is always formatted after publishing the model !

Note

If you use Tabular Editor, it's easy to do it using C# in TE2/3.