With the arrival of UDFs, we must always rethink methods to use calculation teams.
Each are important options when simplifying a semantic mannequin by modularizing logic and decreasing 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 should not usable on the Entrance-Finish.
You could find assets on combining UDFs and calculation teams within the References part beneath.
The query is whether or not we must always add specific measures or solely provide calculation teams to the customers.
Right here is an instance:
I would like to supply the likelihood to calculate the earlier 12 months’s worth.
- I can provide a calculation merchandise that the consumer can use to acquire the specified consequence.
- I can add an specific measure for the PY consequence.
The query is, which one affords extra flexibility and is less complicated to make use of?
That is the query that I’ll attempt to reply right here.
The consumer’s view
First, who’s the consumer?
There are two teams of customers:
- Report designers who use our semantic fashions and wish to know the semantic mannequin simply
- Report customers should perceive what we present within the visualizations with out a lot room for interpretation.
Ultimately, we should assist each consumer teams after we construct a semantic mannequin.
Within the conclusion part beneath, you’ll find my main 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 include the Calendar hierarchy as Rows and the Measures for On-line Gross sales, PY, and PM on the columns.
As well as, I need 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 specific Measures:
As you possibly can see, the outcomes are equivalent.
However discover that the primary column reveals the measure’s title as a substitute of the title of the calculation merchandise, as you possibly can see within the first screenshot.
Specific Measures permits me to change the title displayed within the visible. For instance, the Measures for PY and PM have a unique Title:
That is not possible when utilizing calculation objects. The Visuals at all times present the names of the calculation objects, and I can not rename them.
I don’t even see the title 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 comprises the express measures.
Right here, now we have 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 include duplicate data. Furthermore, within the high variant, you possibly can see the time period “Time Perform” within the Subtitle, which is meaningless to any report client.
Moreover the titles and subtitles, the variations are even smaller in comparison with the matrix visible.
Pivot tables in Excel
Now, let’s have a look at the way it works in Excel Pivot tables:
However right here now we have a difficulty 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 possibly can see, the PY column is Empty, despite the fact that there may be information for the 12 months 2022.
When making an attempt this with specific measures, I bought this consequence:
Even with specific measures, the PY concern persists.
I then added a PY measure utilizing the traditional time intelligence, and it labored, as proven above with the values highlighted in inexperienced.
This factors to a difficulty with Excel and calendar-based time intelligence.
However I can nonetheless rename the measure names, like in Energy BI.
Subsequently, there isn’t a distinction between the 2 variants.
The consumer’s view – once more
After we have a look at the report client, we will create the identical stories with out seeing a distinction.
A minimum of for the easy examples that I confirmed above.
For the report designer, it’s a unique story.
Any such consumer should know methods to use the information mannequin and the calculation teams.
It is a hindrance to self-service BI, the place builders make the information mannequin obtainable, and different customers create their very own stories.
Good documentation on methods to use the information mannequin, together with training and coaching, is crucial when utilizing solely calculation teams as a substitute of providing specific measures.
However we attain limits, for instance, after 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 need to create Excel stories with PivotTables primarily based on the Semantic mannequin.
Once more, we should educate them on methods to use the information mannequin accurately.
That is a lot simpler after we materialize all the required measures and put them into well-structured Show Folders.
The customers can choose the wanted measures and work with them.
Conclusion
As you may have seen, creating specific measures can profit the report designer who works with our semantic fashions.
My guideline when creating semantic fashions is the next:
The consumer’s wants come first.
Technical causes are at all times second.
No technical achieve outweighs the output’s usability and understandability.
Now it’s your flip.
What are your tips through the creation of a semantic mannequin?
What’s your most essential reasoning through the design part?
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 subject with a barely completely different view:
Like in my earlier articles, I exploit the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset without cost from Microsoft right here.
The Contoso Knowledge can be utilized freely beneath the MIT License, as described on this doc. I up to date the dataset to shift the information to up to date dates and eliminated all tables not wanted for this instance.
