Forum Discussion

f1rich's avatar
f1rich
Frequent Visitor
3 years ago
Solved

switch measure dax

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
 
DateAvailabilityMins in day
01/10/2022240440
02/10/2022240440
03/10/20220440
04/10/20220440
05/10/2022400440
06/10/2022400440
07/10/20220440
08/10/20220440
09/10/2022300440
10/10/2022300440
11/10/2022300440
12/10/2022200440
13/10/20220440
14/10/20220440
15/10/20220440
16/10/2022300440
17/10/2022440440
18/10/2022440440
19/10/2022440440
20/10/20220440
21/10/20220440
22/10/20220440
23/10/2022100440
24/10/2022100440
25/10/2022200440
26/10/2022100440
27/10/2022400440
28/10/2022400440
29/10/20220440
30/10/20220440
31/10/20220440

 

Thank you in advanced

  • f1rich's avatar
    f1rich
    3 years ago

    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
    )

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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.

     

    • f1rich's avatar
      f1rich
      Frequent Visitor
      3 years ago

      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

      • f1rich's avatar
        f1rich
        Frequent Visitor
        3 years ago

        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
        )