topic Re: switch measure dax in DAX Commands and Tips

switch measure dax

f1rich — Thu, 12 Jan 2023 13:22:19 GMT

Hi, hope you help me.

I am struggling to get the Switch measure to work, am I missing something?

What i want to show is if date less than 18/10/22 and measure = Sheffield then 440 but if date greater than 18/10/22 and measure = Sheffield then 600 else 1440, below is my formula.

Only 440 shows for all October.

Need to be

Less than 17/10/22, 440

18/10/22 onwards, 600

Mins in a day = SWITCH(TRUE(), SELECTEDVALUE('Measure Selection'[Measure Name])="Sheffield" && SELECTEDVALUE(Date[fDateTime])<DATE(2022, 10, 18), 440,

SELECTEDVALUE('Measure Selection'[Measure Name])="Sheffield" && SELECTEDVALUE(Date[fDateTime])>DATE(2022, 10, 18), 600, 1440)

Results

Date	Availability	Mins in day
01/10/2022	240	440
02/10/2022	240	440
03/10/2022	0	440
04/10/2022	0	440
05/10/2022	400	440
06/10/2022	400	440
07/10/2022	0	440
08/10/2022	0	440
09/10/2022	300	440
10/10/2022	300	440
11/10/2022	300	440
12/10/2022	200	440
13/10/2022	0	440
14/10/2022	0	440
15/10/2022	0	440
16/10/2022	300	440
17/10/2022	440	440
18/10/2022	440	440
19/10/2022	440	440
20/10/2022	0	440
21/10/2022	0	440
22/10/2022	0	440
23/10/2022	100	440
24/10/2022	100	440
25/10/2022	200	440
26/10/2022	100	440
27/10/2022	400	440
28/10/2022	400	440
29/10/2022	0	440
30/10/2022	0	440
31/10/2022	0	440

Thank you in advanced

Re: switch measure dax

Greg_Deckler — Thu, 12 Jan 2023 13:38:25 GMT

f1rich Unable to replicate this behavior. Where is the Date column coming from? From your Date table or from your fact table? Could be that if coming from fact table that if the relationship is one direction then the filtering is not flowing back. See attached PBIX for my attempt to replicate. Below signature.

Re: switch measure dax

f1rich — Thu, 12 Jan 2023 13:57:16 GMT

Hi Greg,

Thanks for your PBIX, this works but needs another filter to pick Sheffield only. I have a measure name which looks at Sheffield, Bradford, York etc. Is there any way i can do this? All are from one table be the way.

Thank you

Re: switch measure dax

f1rich — Thu, 12 Jan 2023 14:37:00 GMT

Hi,

I have finally done it. Don't know why but it works

What i did was duplicate my date (DD/MM/YYYY hh:mm:ss) column and covert it into Date Only (DD/MM/YYYY)

Below is my formula

Mins in a day = SWITCH(TRUE(),

SELECTEDVALUE('Measure Selection'[Measure Name])="Sheffield" && SELECTEDVALUE(Date[Date_Only])<DATE(2022, 10, 18), 440,

SELECTEDVALUE('Measure Selection'[Measure Name])="Sheffield" && SELECTEDVALUE(Date[Date_Only])>=DATE(2022, 10, 18), 600,

1440

)