Using calculation groups or many to many relationships for time intelligence selection
HTML-код
- Опубликовано: 27 фев 2023
- Compare two common techniques to filter time periods in DAX: calculation groups and many-to-many relationships.
Article and download: sql.bi/786473?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... 
Наука
For me, this has been one of the most complex topics since I learn from you. As always, thank you Alberto... a great lecturer!
Yes! I'm the first to comment. I love your videos. Thank you so much for creating them. I learn so much on each watch. ✅
Thank you Alberto, very creative, learned something new I would have never thought of. Intuitively I would have used the CG-solution. One point from my perspective is worth to be mentioned: The CG group solution is more flexible as you said, because you can write DAX-Code. And a side effect of using a calculated table for the M-N solution is that it only considers the absolute max orderdate whereas the CG-solution could consider a filter on the date table by using ALLSELECTED. That would the M-N solution be unable to implement because it defines the used date ranges on load time of the model. I find this an interesting difference between those two solutions worth to be mentioned.
After all these years of telling us to stay away from m2m relationships 😂. I’ll stick to the calc groups. But yea great content as always.
That's great insight on dax query patterns. Well said on it's applicability, test it on YOUR OWN MODEL/MACHINE CONFIG before concluding blindly. Thank you 👍
Of course, all the times!
Amazing insights! To be fair for the calculation group, it has its strengths when you want to build a more interactive report page. It would be just small changes with the calculation group to allow for selecting the last month from a data slicer, whereas the data model solution is static, defined at loading time. You need to be aware in general that when adding interactive or dynamic behavior, calculation times add up or might even mess up efficient storage engine queries. Yes, you can stack up dynamic RLS with some DAX, then allow for switching the language with some DAX supporting to show the right labels, then add dynamic currency conversion including dynamic format strings from a calculation group, all on top of a base measure that might require a virtual table or iterator. But "yes, you can" is not always the smartest decision.
Nice presentation
Thanks for a very interesting video. And for the helpful ideas!
One point: in your video, relationships is not many-to-many. Relationships in Power BI file is many-to-one and "Both". As I understand it, this happened by accident due to unique Dates in the DateTable. Many-to-many work just as well :)
Yes, you can create a many-to-many, but the principle is that the relationship between Period and Sales is a many-to-many (obtained through a chain of many-to-one + one-to-many).
@@SQLBI Exactly! Now it's even clearer. Thank you!
very inspirational video as always love your work. I am so novice that even after following the config steps within DAX, I am getting same figures for every period. Could you please help understand what am I doing wrong . thankyou
actually my problem is that when i build relationship between my date table and the issues table [in which i need the period level results], the date columns of these table loose the date hierarchy. How do i fix this ? thankyou
I love that PowerBI supports such a deep developer experience. My question is a difficult one. I run on a Fiscal Year that begins Sep 1. I need to do most of my reporting with fiscal year and fiscal quarter analysis. While I have a custom date table that include fiscal year number, fiscal quarter, and fiscal month I need to be able to do period over period reporting. How would you suggest modifying either of the approaches you use here to do that?
Thank you in advance!
You might just use the regular time intelligence functions. You can find many examples here: www.daxpatterns.com/time-patterns/
You can also use Bravo to generate measures automatically with one of the available templates: bravo.bi
Could you please make a video on this? I have a fact table with start_year (2020) and end_year(2023) parameters in it to limit the number of years worth of data. On top of this I have incremental refresh of last 13 months. I publish this file to web service and change the start_year parameter say from 2020 to 2019. Why does the parameter changes in web service doesn't take into effect? If I change from 2020 to 2021, it does make necessary changes but for the first time only after publishing the file. Any thought on this? My report should only hold only last 5 years of data with last 13 months of incremental refresh.
Thanks Alberto. What is the difference between previous year and last year ?
Assuming the last date is 12/31/2023...
Last Year = Jan 1 2023 to Dec 31 2023
Prev Year = Jan 1 2022 to Dec 31 2022
Last Month = Dec 1 2023 to Dec 31 2023
Prev Month = Nov 1 2023 to Nov 30 2023
Last 6 Months = Jul 1 2023 to Dec 31 2023
Prev 6 Month = Jan 1 2023 to Jun 30 2023