# Formatting DAX Expression Returned By SemPy in Fabric

There is an [old Italian saying](https://www.youtube.com/watch?v=M2p1RdlVX54) "*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](https://www.daxformatter.com/) available via an [API](https://www.sqlbi.com/blog/marco/2014/02/24/how-to-pass-a-dax-query-to-dax-formatter/). 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**

```python
!pip install beautifulsoup4 semantic-link  --q
```

**Get a list of measures for a dataset**

```python
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)
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698682998625/ad7cdccd-8ef3-4597-9dd7-f5bddbd0b59a.png align="center")

**Function to call the DAX Formatter API**

```python
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:

```python
#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")
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698683323944/fe05c42f-92be-4f30-acd5-f4cb8819954a.png align="center")

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.

```python
##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)
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1698683595041/ed843412-3aca-412f-8065-ad8786c08e6f.png align="center")

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">The default is short line but you can change it by <code>line="long"</code></div>
</div>

```python
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.