Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I want to display "Month-over-month change" measure when Matrix is expanded to months, then when User selects "Drill up" I want to display "Quarter-over-quarter change" measure and finally when user selects "Drill up" again I want to display "Year-over-year change" measure.
So in a fully expanded Matrix I have a correct "Month-over-month change" measure:
Then on a "Drill up" action I get what's on the left image but I want what's on the right one.
Bellow is my "Month-over-month change" DAXmeasure, it differs from the QoQ and YoY only by a period that is subtracted in a DATEADD function:
MoM% =
VAR __PREV_MONTH = CALCULATE(SUM('Table'[quantity]), DATEADD('Date'[Date], -1, MONTH))
RETURN
IF(
ISBLANK(SUM('Table'[quantity])),
BLANK(),
DIVIDE(
SUM('Table'[quantity]) - __PREV_MONTH,
__PREV_MONTH
)
)
I tried using ISFILTERED and ISINSCOPE but it seems that they don't react to "Drill up" action.
Changing from one mesaure to another does not have to necessarily response directly to a "Drill up" action, it will be ok if I provide a seperate interface via single select slicer.
Im using a seperate date dim table 'Date' with a relationship to a date column in a source table 'Table'.
Solved! Go to Solution.
I managed to resolve this by using ISINSCOPE function (no idea why this did not work the first time I tried it).
I simply created a seperate measure with a switch statement:
PPG% =
SWITCH(TRUE(),
ISINSCOPE('Date'[PL Calendar Month]),[Value_grouped MoM%],
ISINSCOPE('Date'[Calendar Quarter]),[Value_grouped QoQ%],
[Value_grouped YoY%]
)
I managed to resolve this by using ISINSCOPE function (no idea why this did not work the first time I tried it).
I simply created a seperate measure with a switch statement:
PPG% =
SWITCH(TRUE(),
ISINSCOPE('Date'[PL Calendar Month]),[Value_grouped MoM%],
ISINSCOPE('Date'[Calendar Quarter]),[Value_grouped QoQ%],
[Value_grouped YoY%]
)
User | Count |
---|---|
77 | |
74 | |
60 | |
60 | |
46 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |