Create and maintain Semantic model measures and calculation groups using Sempy + Sempy Labs

In my last post, I discussed how I've been using Sempy and SemPy Labs to convert column names to Snake Case. This post will explore the next important step: creating and managing measures.

Having all measures simplifies processes significantly; it makes changes easier, makes smoother documentation, enhances AI productivity and facilitates deployment to a new semantic model. What I appreciate most is the ability to add descriptions and organise measures into folders effortlessly with AI, then publish these descriptions to the Semantic Model, which will undoubtedly delight self-service BI developers. Another excellent use case is that it can function as documentation and can be shared via Fabric Org Apps. While it might not be for all users, it can serve as valuable technical documentation.

The process is quite similar to the previous post, and again, inspiration comes from SemPy Labs Github Page.

Steps:

• Install the necessary libraries

import sempy_labs as labs
from sempy_labs.tom import connect_semantic_model

• Connect to the Semantic Model

	workspace = 'Workspace ID'
	dataset = 'Semantic Model/Dataset Name'

• Create the Measures

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for t in tom.model.Tables:
        if t.Name == 'Table Name':
            tom.add_measure(table_name=t.Name, measure_name='Measure Name', expression="DAX Expression")
   

In our daily data development process, we frequently don’t have a complete list of measures from the beginning; we typically add them as we go. To improve the script, we can incorporate a check to see if a measure already exists, which will help us simplify the process. If a measure is found, we will update it accordingly. To implement this, we will slightly modify the above code.

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for t in tom.model.Tables:
        if t.Name == 'Table Name':
            # List of measures to add or update
            measures = [
                {
                    'name': 'Measure Name',
                    'expression': "DAX Expression",
                    'format_string': "#,0.0",
                    'description': "Measure Description",
                    'display_folder': "Measure Folder"
                }
            ]

            # Add or update measures
            for measure in measures:
                if measure['name'] in [m.Name for m in t.Measures]:
                    # Update existing measure
                    existing_measure = t.Measures[measure['name']]
                    existing_measure.Expression = measure['expression']
                    existing_measure.FormatString = measure['format_string']
                    existing_measure.Description = measure['description']
                    existing_measure.DisplayFolder = measure['display_folder']
                else:
                    # Add new measure
                    tom.add_measure(
                        table_name=t.Name, 
                        measure_name=measure['name'], 
                        expression=measure['expression'], 
                        format_string=measure['format_string'],
                        description=measure['description'],
                        display_folder=measure['display_folder']
                    )

The same logic applies to calculation groups, too.

# Define the calculation group
with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    
    calculation_group_name = "Time Intelligence"
    precedence=1,
    description="Calculation group for Time Intelligence",
    hidden=False
    calculation_items = [
        {
            "table_name": "Europe Weather Data",
            "name": "Current",
            "expression": "SELECTEDMEASURE()"
        },
        {
            "table_name": "Europe Weather Data",
            "name": "PY",
            "expression": "CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date Table'[Year]))"
        },
        {
            "table_name": "Europe Weather Data",
            "name": "YOY",
            "expression": "SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Name] = \"PY\")"
        },
        {
            "table_name": "Europe Weather Data",
            "name": "YOY%",
            "expression": "DIVIDE(CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Name]=\"YOY\"), CALCULATE(SELECTEDMEASURE(), 'Time Intelligence'[Name]=\"PY\"))"
        }
    ]

And I do similar checks to see if already exists, if yes, update it.

    # Retrieve all calculation groups
    all_calculation_groups = tom.all_calculation_groups()

    # Check if the calculation group already exists
    existing_group = next((cg for cg in all_calculation_groups if cg.Name == calculation_group_name), None)

    if existing_group:
        # Update existing calculation group
        existing_group.CalculationItems = calculation_items
        print(f"Updated calculation group: {calculation_group_name}")
    else:
        # Add new calculation group
        calculation_group = {
            "name": calculation_group_name,
            "calculationItems": calculation_items
        }
        tom.add_calculation_group(
            name=calculation_group_name,  
            precedence=1,
            description="DESC ",
            hidden=False
        )

        for item in calculation_items:
            tom.add_calculation_item(
                table_name=calculation_group_name,
                calculation_item_name=item['name'],
                expression=item['expression']
            )
        print(f"Added new calculation group: {calculation_group_name}")

Here is the link to a Notebook, which has an example - https://bit.ly/Update_Semnatic_Model_Measures_and_Calculation_Groups

Some points to note:

• Keep in mind that these scripts are not intended to run on a schedule; they are meant for development purposes and should be stored in development repositories for future reference.

• One challenging aspect of using a Notebook compared to designing scripts is the ability to view data, assess expressions, and debug them. For complex DAX measures, I typically create my measures in the Model View. Once I'm satisfied with the results, I transfer them to the Notebook. Although it's a bit tedious, having all my measures in the Notebook makes it easier to hand over to someone at the client's site as a consultant.

• While we could combine all measures into one code block, I prefer to organise them by table to enhance readability and maintainability. As developers, I believe it's our responsibility to simplify rather than overcomplicate.

• I use a small script to delete all measures when I need to modify multiple measures instead of doing it one by one in the model. However, I wouldn't recommend this approach without fully understanding its impact. It works well when I'm the only person working on a project, as it provides a quick way to remove existing measures and organise new measures into the appropriate folders or descriptions.

with connect_semantic_model(dataset=dataset, readonly=False, workspace=workspace) as tom:
    for t in tom.model.Tables:
        if t.Name == 'Europe Weather Data':
            # List of measures to delete
            measures_to_delete = [m for m in t.Measures]
            
            # Delete each measure
            for measure in measures_to_delete:
                tom.remove_object(measure)

Keep smiling,

Prathy :)