Forum Discussion
Measure in SWITCH function suddenly ignores Date context: outside of switch it works. Why is this?
Hi Guido_Beulen
Thank you for reaching out to the Microsoft Fabric Forum Community.
Elena_Kalina Thanks for your inputs.
Guido_Beulen - please try below DAX, it may helps you,
Most Recent Score =
SWITCH(
TRUE(),
'1 Kwaliteit'[Koppelcode KPI] = "2.8.2.",
FORMAT(
CALCULATE(
[Gemiddelde beschikbaarheid],
KEEPFILTERS('0 DIM Datum'),
FILTER(
'1 2 Maas koffie details',
'1 2 Maas koffie details'[Datum] =
CALCULATE(
MAX('1 2 Maas koffie details'[Datum]),
KEEPFILTERS('0 DIM Datum')
)
)
),
"00.0%"
),
BLANK()
)
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you
Here's a picture of the measure in the table, with the Debug measures and the slicer around. Outside of the SWITCH-table it shows as 99,6 which is correct, inside it says 99,8 which is the Q2 value.
Hi Guido_Beulen
The problem occurs because the SWITCH function evaluates its conditions before the rest of the measure calculation, the inner CALCULATE in your [2.8.2. Latest Score] measure loses the outer filter context when evaluated inside SWITCH and your date variables (Mindate/Maxdate) are evaluated in a different context than expected
Try to use KEEPFILTERS to preserve context
Most Recent Score = VAR Mindate = MIN('0 DIM Datum'[Date]) VAR Maxdate = MAX('0 DIM Datum'[Date]) RETURN SWITCH( TRUE(), [2.8.1.], '1 Kwaliteit'[Koppelcode KPI] = "2.8.2.", FORMAT( CALCULATE( [Gemiddelde beschikbaarheid], KEEPFILTERS('0 DIM Datum'[Date] >= Mindate && '0 DIM Datum'[Date] <= Maxdate), FILTER( '1 2 Maas koffie details', '1 2 Maas koffie details'[Datum] = MAX('1 2 Maas koffie details'[Datum]) ) ), "00.0%" ), BLANK() )
I was expecting something like this to be the issue, although it seems like I need to use to KEEPFILTERS somewhere in the SWITCH Function itself for it to work. Now tried your suggested setup but it still does not keep the filter context intact within the SWITCH function.
Thank you very much for your help though! Any followup ideas?
Hi Guido_Beulen
Thank you for reaching out to the Microsoft Fabric Forum Community.
Elena_Kalina Thanks for your inputs.
Guido_Beulen - please try below DAX, it may helps you,
Most Recent Score =
SWITCH(
TRUE(),
'1 Kwaliteit'[Koppelcode KPI] = "2.8.2.",
FORMAT(
CALCULATE(
[Gemiddelde beschikbaarheid],
KEEPFILTERS('0 DIM Datum'),
FILTER(
'1 2 Maas koffie details',
'1 2 Maas koffie details'[Datum] =
CALCULATE(
MAX('1 2 Maas koffie details'[Datum]),
KEEPFILTERS('0 DIM Datum')
)
)
),
"00.0%"
),
BLANK()
)
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you
