With the advent of UDFs, we have to rethink how to use calculation groups.
Both are important aspects when simplifying the semantic model by combining logic and reducing duplication of business logic.
While UDFs are more useful for modeling business logic and having it only once in a data model, calculation groups are useful for report designers to apply business logic to calculations.
Calculation Groups are visible in Report Designers, but UDFs are only usable within DAX expressions and not usable in the Front-End.
You can find resources on combining UDFs with calculation groups in the references section below.
The question is whether we should add explicit measurements or only provide calculation groups to users.
Here is an example:
I need to provide an opportunity to calculate the value of the previous year.
- I can provide a calculator that the user can use to get the desired result.
- I can add a clear measure of the effect of PY.
The question is, which one offers more flexibility and is easier to use?
This is the question I will try to answer here.
User 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 buyers should understand what we are showing at a glance without much room for interpretation.
Finally, we must support both groups of users when building a semantic model.
In the conclusion section below, you will find my main guide when designing a semantic model.
But first, let's see the results of these two methods for our users.
Using Matrix Visualizations
First, I created the Matrix.
The Matrix should contain the calendar category as rows and the online sales measures, PY, and PM in the columns.
In addition, I want to cut results with Brand.
First, I did it with calculators:
The result is as needed.
Note that I have to filter the counters to remove the PY (Week) Item, as it would cause an error when used with Months and Months.
Next, I did it in clear steps:
As you can see, the results are the same.
But notice that the first column shows the name of the measure instead of the name of the calculation object, as you can see in the first screenshot.
Clear Steps allow me to change the name displayed on the visual. For example, Steps PY and PM have a different name:
This is not possible when using calculators. The visualization always shows the names of the counters, and I can't rename them.
I don't even see the name of the first rate.
So, I have to add a sound header to the Visual. But I recommend doing this anyway.
Using other view types (columns or bars)
Next, I did it with column images:
The top view uses counters, and the bottom view contains clear measurements.
Here, we have the same situation as before:
- I have to add a filter to the top view counters.
- I can rename the dimensions in the screen below.
But the results are still the same.
I left the default title and legend area. You can see that they need to be replaced, as they contain duplicate information. In addition, in the top variant, you can see the word “Time Work” in the subheading, which means nothing to any buyer of the report.
Except for titles and subtitles, the differences are even smaller when compared to matrix views.
Pivot Tables in Excel
Now, let's see how it works in Excel Pivot tables:
But here we have problem with PY Calculation Item:
What doesn't change is the need to filter counters to store only the items that are needed:
As you can see, the PY column is empty, even if there is data for the year 2022.
When I tried this with clear steps, I got this result:
Even with clear paths, the PY issue persists.
I then added the PY ratio using good old fashioned ingenuity, and it worked, as shown above with the values highlighted in green.
This points to a problem with Excel and calendar-based time management.
But I can still rename measurements, like in Power BI.
Therefore, there is no difference between these two divisions.
User view – again
If we look at the consumer report, we can create similar reports without seeing the difference.
At least with the simple examples I showed above.
For the report builder, it's a different story.
This type of user should know how to use the data model and 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 of how to use the data model, as well as education and training, is essential if only calculation groups are used instead of providing clear steps.
But we reach limits, for example, if we try to sort the data on a scale that does not exist, because it is only available with a count object.
The same applies to Excel users who want to create Excel reports with PivotTables based on the Semantic model.
Also, we have to teach them how to use the data model correctly.
This is very easy if we make all the necessary measurements and put them in well organized Display Folders.
Users can select the required steps and work with them.
The conclusion
As you can see, making clear steps can benefit the report designer working with our semantic models.
My guideline when creating semantic models is the following:
User needs come first.
Technical reasons are always secondary.
There is no technical benefit more than the usability and comprehensibility of the output.
Now it's your turn.
What are your guidelines when creating a semantic model?
What is your most important reason during the design phase?
References
SQLBI article, which compares UDF and calculation groups and shows how they can be combined.
And here is the video for this article:
Here, a video from Guys in a cube about the same topic from a slightly different perspective:
As in my previous articles, I am using the Contoso sample data. You can download the ContosoRetailDW Dataset for free from Microsoft here.
Contoso data can be freely used under the MIT license, as described in this document. I updated the data set to change the data to modern dates and remove all the tables that are not needed for this example.
