Forum Discussion
Different measures for different drill downs/ups?
I have a measure for day on day change. I converted the date to date hierachy and obvious for the day, it works perfect. However, when I drill up my charts to month or quarter or year level then it doesn't show the month on month change right values which makes sense given the measure is day on day. Is there a way to make the measure being applied to also change to month on month, quarter on quarter or year on year depending on the drill view?
Hi afaro ,
Create Separate Measures for Each Time Granularity: You'll need to create individual measures for day on day, month on month, quarter on quarter, and year on year changes. Each of these measures will calculate the change based on the respective time granularity.
Use SWITCH() or IF() Statements for Dynamic Measure Selection: To dynamically switch between these measures based on the current drill level, you can use a combination of SWITCH() or IF() statements within a new measure. This measure will check the current drill level and return the appropriate measure.
Dynamic Change Measure = SWITCH( TRUE(), ISINSCOPE('Date'[Day]), [Day on Day Change], ISINSCOPE('Date'[Month]), [Month on Month Change], ISINSCOPE('Date'[Quarter]), [Quarter on Quarter Change], ISINSCOPE('Date'[Year]), [Year on Year Change], BLANK() )How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi afaro ,
Create Separate Measures for Each Time Granularity: You'll need to create individual measures for day on day, month on month, quarter on quarter, and year on year changes. Each of these measures will calculate the change based on the respective time granularity.
Use SWITCH() or IF() Statements for Dynamic Measure Selection: To dynamically switch between these measures based on the current drill level, you can use a combination of SWITCH() or IF() statements within a new measure. This measure will check the current drill level and return the appropriate measure.
Dynamic Change Measure = SWITCH( TRUE(), ISINSCOPE('Date'[Day]), [Day on Day Change], ISINSCOPE('Date'[Month]), [Month on Month Change], ISINSCOPE('Date'[Quarter]), [Quarter on Quarter Change], ISINSCOPE('Date'[Year]), [Year on Year Change], BLANK() )How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.