With the appearance of UDFs, we must always rethink methods to use calculation teams.
Each are important options when simplifying a semantic mannequin by modularizing logic and lowering enterprise logic duplication.
Whereas UDFs are very helpful for standardizing enterprise logic and having it solely as soon as per information mannequin, calculation teams are helpful for report designers to use enterprise logic to measures.
Calculation Teams are seen to Report Designers, however UDFs are usable solely inside DAX expressions and aren’t usable on the Entrance-Finish.
Yow will discover sources on combining UDFs and calculation teams within the References part under.
The query is whether or not we must always add express measures or solely provide calculation teams to the customers.
Right here is an instance:
I want to supply the likelihood to calculate the earlier 12 months’s worth.
- I can provide a calculation merchandise that the person can use to acquire the specified outcome.
- I can add an express measure for the PY outcome.
The query is, which one provides extra flexibility and is simpler to make use of?
That is the query that I’ll attempt to reply right here.
The person’s view
First, who’s the person?
There are two teams of customers:
- Report designers who use our semantic fashions and wish to grasp the semantic mannequin simply
- Report customers should perceive what we present within the visualizations with out a lot room for interpretation.
In the long run, we should help each person teams once we construct a semantic mannequin.
Within the conclusion part under, you’ll find my major guideline when designing a semantic mannequin.
However first, let’s see the consequences of the 2 approaches for our customers.
Utilizing Matrix Visualizations
First, I constructed a Matrix.
The Matrix ought to comprise the Calendar hierarchy as Rows and the Measures for On-line Gross sales, PY, and PM on the columns.
As well as, I wish to slice the outcomes by Model.
First, I did it with Calculation Gadgets:
The result’s as wanted.
Discover that I need to filter the calculation objects to exclude the PY (Week) Merchandise, as it could trigger an error when used with Quarters and Months.
Subsequent, I did it with express Measures:
As you’ll be able to see, the outcomes are similar.
However discover that the primary column reveals the measure’s identify as an alternative of the identify of the calculation merchandise, as you’ll be able to see within the first screenshot.
Express Measures permits me to switch the identify displayed within the visible. For instance, the Measures for PY and PM have a special Identify:
That is not possible when utilizing calculation objects. The Visuals all the time present the names of the calculation objects, and I can not rename them.
I don’t even see the identify of the unique measure.
Subsequently, I need to add a significant title to the Visible. However I like to recommend doing this anyway.
Utilizing different visualization sorts (columns or bars)
Subsequent, I did it with column visuals:
The highest visible makes use of the calculation objects, and the underside visible incorporates the specific measures.
Right here, we’ve the identical scenario as earlier than:
- I need to add a filter on the calculation objects for the highest visible.
- I can rename the measures within the backside visible.
However the outcomes are nonetheless the identical.
I left the default title and legend place. You’ll be able to see that they should be modified, as they comprise duplicate info. Furthermore, within the prime variant, you’ll be able to see the time period “Time Perform” within the Subtitle, which is meaningless to any report shopper.
Moreover the titles and subtitles, the variations are even smaller in comparison with the matrix visible.
Pivot tables in Excel
Now, let’s take a look at the way it works in Excel Pivot tables:
However right here we’ve a problem with the PY Calculation Merchandise:
What doesn’t change is the necessity to filter the calculation objects to maintain solely the wanted objects:
As you’ll be able to see, the PY column is Empty, although there’s information for the 12 months 2022.
When attempting this with express measures, I obtained this outcome:
Even with express measures, the PY subject persists.
I then added a PY measure utilizing the basic time intelligence, and it labored, as proven above with the values highlighted in inexperienced.
This factors to a problem with Excel and calendar-based time intelligence.
However I can nonetheless rename the measure names, like in Energy BI.
Subsequently, there is no such thing as a distinction between the 2 variants.
The person’s view – once more
Once we take a look at the report shopper, we are able to create the identical studies with out seeing a distinction.
At the very least for the easy examples that I confirmed above.
For the report designer, it’s a special story.
This kind of person should know methods to use the info mannequin and the calculation teams.
It is a hindrance to self-service BI, the place builders make the info mannequin obtainable, and different customers create their very own studies.
Good documentation on methods to use the info mannequin, together with training and coaching, is crucial when utilizing solely calculation teams as an alternative of providing express measures.
However we attain limits, for instance, once we attempt to filter information by a nonexistent measure, as a result of it’s solely obtainable with a calculation merchandise.
The identical applies to Excel customers who wish to create Excel studies with PivotTables based mostly on the Semantic mannequin.
Once more, we should educate them on methods to use the info mannequin accurately.
That is a lot simpler once we materialize all the mandatory measures and put them into well-structured Show Folders.
The customers can decide the wanted measures and work with them.
Conclusion
As you’ve seen, creating express measures can profit the report designer who works with our semantic fashions.
My guideline when creating semantic fashions is the next:
The person’s wants come first.
Technical causes are all the time second.
No technical acquire outweighs the output’s usability and understandability.
Now it’s your flip.
What are your pointers through the creation of a semantic mannequin?
What’s your most necessary reasoning through the design section?
References
The SQLBI article, which compares UDF and calculation teams and reveals methods to mix them.
And right here is the video for this text:
Right here, the video from Guys in a dice about the identical matter with a barely totally different view:
Like in my earlier articles, I take advantage of the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset without spending a dime from Microsoft here.
The Contoso Information can be utilized freely below the MIT License, as described in this document. I up to date the dataset to shift the info to up to date dates and eliminated all tables not wanted for this instance.
