Measure in SWITCH function suddenly ignores Date context: outside of switch it works. Why is this?

[Guido_Beulen]

Hi all, 

I have a measure that calculates the latest score for a variable (expressed as a percentage) based on the last available date that has data, looking like:

2.8.2. Latest Score =

CALCULATE(

[Gemiddelde beschikbaarheid], FILTER(ALLSELECTED('0 DIM Datum'),'0 DIM Datum'[Date]>=MIN('0 DIM Datum'[Date]) && '0 DIM Datum'[Date]<=MAX('0 DIM Datum'[Date])),FILTER('1 2 Maas koffie details','1 2 Maas koffie details'[Datum] = MAX('1 2 Maas koffie details'[Datum]))).

This measure in a table or card work fine with my date slicer and returns the latest value available in the selected time period. 

When I put this in my SWITCH function, it suddenly ignores date context. The SWITCH function looks like:

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([2.8.2. Latest Score],"00.0%"),

BLANK() )

I also tried hardcoding the date context in the SWITCH function, but it still ignores the slicer: 

/FORMAT(CALCULATE( [Maas Gemiddelde beschikbaarheid],FILTER(ALLSELECTED('0 DIM Datum'),'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%")

[v-priyankata]

Hi @Guido_Beulen 

I hope the information shared was helpful to you. If your question has been answered, kindly mark the most relevant reply as the Accepted Solution. This small action can make a big difference for others who are looking for the same solution.

[v-priyankata]

Hi @Guido_Beulen 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[v-priyankata]

Hi @Guido_Beulen 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted  If yes, marking the relevant solution would be awesome for others who might run into the same thing.

[Guido_Beulen]

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. 

[Elena_Kalina]

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()
)

[Guido_Beulen]

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? 

[v-priyankata · Accepted solution]

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