Close Menu
    AI Tools

    Comparing Explicit Measures to Calculation Groups in Tabular Models

    By No Comments7 Mins Read
    Comparing Explicit Measures to Calculation Groups in Tabular Models

    With the advent of UDFs, we should rethink how to use calculation groups.

    Both are essential features when simplifying a semantic model by modularizing logic and reducing business logic duplication.

    While UDFs are very useful for standardizing business logic and having it only once per data model, calculation groups are useful for report designers to apply business logic to measures.

    Calculation Groups are visible to Report Designers, but UDFs are usable only within DAX expressions and are not usable on the Front-End.

    You can find resources on combining UDFs and calculation groups in the References section below.

    The question is whether we should add explicit measures or only offer calculation groups to the users.

    Here is an example:

    I need to offer the possibility to calculate the previous year’s value.

    • I can offer a calculation item that the user can use to obtain the desired result.
    • I can add an explicit measure for the PY result.

    The question is, which one offers more flexibility and is easier to use?

    This is the question that I will try to answer here.

    The user’s view

    First, who is the user?

    There are two groups of users:

    • Report designers who use our semantic models and need to understand the semantic model easily
    • Report consumers must understand what we show in the visualizations without much room for interpretation.

    In the end, we must support both user groups when we build a semantic model.

    In the conclusion section below, you will find my primary guideline when designing a semantic model.

    But first, let’s see the effects of the two approaches for our users.

    Using Matrix Visualizations

    First, I built a Matrix.

    The Matrix should contain the Calendar hierarchy as Rows and the Measures for Online Sales, PY, and PM on the columns.

    In addition, I want to slice the results by Brand.

    First, I did it with Calculation Items:

    Figure 1 – Matrix with the needed results. The Calculation Items must be filtered to include only the needed Items (Figure by the Author)

    The result is as needed.

    Notice that I must filter the calculation items to exclude the PY (Week) Item, as it would cause an error when used with Quarters and Months.

    Next, I did it with explicit Measures:

    Figure 2 – The same Matrix as before, but this time with explicit Measures (Figure by the Author)

    As you can see, the results are identical.

    But notice that the first column shows the measure’s name instead of the name of the calculation item, as you can see in the first screenshot.

    Explicit Measures allows me to modify the name displayed in the visual. For example, the Measures for PY and PM have a different Name:

    Figure 3 – The Labels of the explicit measures with the original names (Figure by the Author)

    This is impossible when using calculation items. The Visuals always show the names of the calculation items, and I cannot rename them.

    I don’t even see the name of the original measure.

    Therefore, I must add a meaningful title to the Visual. But I recommend doing this anyway.

    Using other visualization types (columns or bars)

    Next, I did it with column visuals:

    Figure 4 – Column visuals with calculation items (top) and explicit measures (bottom) (Figure by the Author)

    The top visual uses the calculation items, and the bottom visual contains the explicit measures.

    Here, we have the same situation as before:

    • I must add a filter on the calculation items for the top visual.
    • I can rename the measures in the bottom visual.

    But the results are still the same.

    I left the default title and legend position. You can see that they need to be changed, as they contain duplicate information. Moreover, in the top variant, you can see the term “Time Function” in the Subtitle, which is meaningless to any report consumer.

    Besides the titles and subtitles, the differences are even smaller compared to the matrix visual.

    Pivot tables in Excel

    Now, let’s look at how it works in Excel Pivot tables:

    But here we have an issue with the PY Calculation Item:

    Figure 5 – Excel PivotTable while using Calculation items. Note the missing PY values for the first shown year, even though there are values (Figure by the Author)

    What doesn’t change is the need to filter the calculation items to keep only the needed items:

    Figure 6 – Filter out the calculation item PY (Weeks), as it isn’t needed (Figure by the Author)

    As you can see, the PY column is Empty, even though there is data for the year 2022.

    When trying this with explicit measures, I got this result:

    Figure 7 – Excel PivotTable while using explicit measures. The PY issue persists (marked in red), but doesn’t appear when using measures using classic time intelligence (marked in green) (Figure by the Author)

    Even with explicit measures, the PY issue persists.

    I then added a PY measure using the classic time intelligence, and it worked, as shown above with the values highlighted in green.

    This points to an issue with Excel and calendar-based time intelligence.

    But I can still rename the measure names, like in Power BI.

    Therefore, there is no difference between the two variants.

    The user’s view – again

    When we look at the report consumer, we can create the same reports without seeing a difference.

    At least for the simple examples that I showed above.

    For the report designer, it’s a different story.

    This type of user must know how to use the data model and the calculation groups.

    This is a hindrance to self-service BI, where developers make the data model available, and other users create their own reports.

    Good documentation on how to use the data model, along with education and training, is imperative when using only calculation groups instead of offering explicit measures.

    But we reach limits, for example, when we try to filter data by a nonexistent measure, because it’s only available with a calculation item.

    The same applies to Excel users who want to create Excel reports with PivotTables based on the Semantic model.

    Again, we must educate them on how to use the data model correctly.

    This is much easier when we materialize all the necessary measures and put them into well-structured Display Folders.

    The users can pick the needed measures and work with them.

    Conclusion

    As you have seen, creating explicit measures can benefit the report designer who works with our semantic models.

    My guideline when creating semantic models is the following:

    The user’s needs come first.
    Technical reasons are always second.
    No technical gain outweighs the output’s usability and understandability.

    Now it’s your turn.

    What are your guidelines during the creation of a semantic model?

    What is your most important reasoning during the design phase?

    References

    The SQLBI article, which compares UDF and calculation groups and shows how to combine them.

    And here is the video for this article:

    Here, the video from Guys in a cube about the same topic with a slightly different view:

    Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

    The Contoso Data can be used freely under the MIT License, as described in this document. I updated the dataset to shift the data to contemporary dates and removed all tables not needed for this example.

    Share.

    Related Posts

    Add A Comment
    Leave A Reply