Search for a command to run...
If it's not formatted, it's not DAX...
Principal Program Manager, Microsoft Fabric CAT helping users and organizations build scalable, insightful, secure solutions. Blogs, opinions are my own and do not represent my employer.
Hi Sandeep,
Wondering whether you can explain exactly what this is...
import Microsoft.AnalysisServices.Tabular as tom
... the .Net tabular libraries are auto-loaded in the pyspark session?
Thanks!
Last week Microsoft released an open-source text embedding model called Harrier in three sizes- 270M, 0.6B and 27B. I have been testing it in my RAG pipeline and so far it has crushed all my metrics.
For Power BI Copilot and Data agents with semantic models, you must use Prep Data for AI configuration to ground the responses in the context added in Prep for AI. In this blog, I will show you how yo
At FabCon Atlanta last week, the updated notebook Copilot for data engineering and data science was announced. It brings agentic capabilities to the Copilot and is much more intelligent and Fabric-awa
Using Fabric data agent Python SDK
Use workspace monitoring to monitor MCP server use
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.
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
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")
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)
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)
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)
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 !
If you use Tabular Editor, it's easy to do it using C# in TE2/3.