Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Switch True Statement Help Needed

I am trying to use the below measure to categorize some ticket data and for some reason this measure is not picking up all of the And statements.  Any idea why?  Is there some limit Im not aware of?...
Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    Hi blueranger44

    Please consider these 2 solutions then click the solved and thumbs up button to leave kudos because we are unpaid Power BI volunteers.

    Click here for example solutions 



    Solution1:- Simplify the DAX measure.

    You dont need the ANDs because SWITCH is a nested IF.

    So there is no need to test if values < than a previous test.
    This will be much quicker for the CPU and easier for a human to check

     ....

    Sprint1=
    VAR mydate = SELECTEDVALUE(HX/Silvis ALL Tasks Closed'[result.closed_at].[Date])
    RETURN
    SWITCH(True(),
    mydate < Date(2021,6,23), BLANK(),
    mydate <= DATE(2021, 7,7),21.4,
    mydate <= DATE(2021, 7,21),21.4-2,
    mydate <= DATE(2021, 8,4),21.4-3,
    mydate <= DATE(2021, 8,18),21.4-4,
    mydate <= DATE(2021, 9,1),21.5-1,
    mydate <= DATE(2021, 9,15),21.5-2,
    mydate <= DATE(2021, 9,29), 21.5-3,
    mydate <= DATE(2021, 10,13), 21.5-4,
    mydate <= DATE(2021, 10,27), 21.6-1,
    mydate <= DATE(2021, 11,10), 21.6-2,
    mydate <= DATE(2021, 11,24), 21.6-3,
    mydate <= DATE(2021, 1,3), 21.7,
    mydate <= DATE(2022, 1,19),22.1,
    mydate <= DATE(2022, 2,02),22.2,
    mydate <= DATE(2022, 2,16),22.3,
    mydate <= DATE(2022, 3,2),22.4,
    mydate <= DATE(2022, 3,16),22.5,
    mydate <= DATE(2022, 3,30),22.6,
    mydate <= DATE(2022, 4,13),22.7,
    mydate <= DATE(2022, 04,27),22.8,
    BLANK()) 


    Solution2:- Use Calendar table

    This solution is much simpler.

    Create a Calander with Date and sprint value.

    Then create a 1:m relationship between the Calander[Date] and your table[Date].
    This solution is better than a DAX formula.

     

    Click here for example solutions 

     

speedramps's avatar
speedramps
Super User
4 years ago

Hi blueranger44

Please consider these 2 solutions then click the solved and thumbs up button to leave kudos because we are unpaid Power BI volunteers.

Click here for example solutions 



Solution1:- Simplify the DAX measure.

You dont need the ANDs because SWITCH is a nested IF.

So there is no need to test if values < than a previous test.
This will be much quicker for the CPU and easier for a human to check

 ....

Sprint1=
VAR mydate = SELECTEDVALUE(HX/Silvis ALL Tasks Closed'[result.closed_at].[Date])
RETURN
SWITCH(True(),
mydate < Date(2021,6,23), BLANK(),
mydate <= DATE(2021, 7,7),21.4,
mydate <= DATE(2021, 7,21),21.4-2,
mydate <= DATE(2021, 8,4),21.4-3,
mydate <= DATE(2021, 8,18),21.4-4,
mydate <= DATE(2021, 9,1),21.5-1,
mydate <= DATE(2021, 9,15),21.5-2,
mydate <= DATE(2021, 9,29), 21.5-3,
mydate <= DATE(2021, 10,13), 21.5-4,
mydate <= DATE(2021, 10,27), 21.6-1,
mydate <= DATE(2021, 11,10), 21.6-2,
mydate <= DATE(2021, 11,24), 21.6-3,
mydate <= DATE(2021, 1,3), 21.7,
mydate <= DATE(2022, 1,19),22.1,
mydate <= DATE(2022, 2,02),22.2,
mydate <= DATE(2022, 2,16),22.3,
mydate <= DATE(2022, 3,2),22.4,
mydate <= DATE(2022, 3,16),22.5,
mydate <= DATE(2022, 3,30),22.6,
mydate <= DATE(2022, 4,13),22.7,
mydate <= DATE(2022, 04,27),22.8,
BLANK()) 


Solution2:- Use Calendar table

This solution is much simpler.

Create a Calander with Date and sprint value.

Then create a 1:m relationship between the Calander[Date] and your table[Date].
This solution is better than a DAX formula.

 

Click here for example solutions 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks speedramps for your response.  I tried your code and Im getting all blanks?

    Sprint = 
VAR mydate = SELECTEDVALUE('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date])
RETURN
SWITCH(True(),
	mydate < Date(2021,6, 23), BLANK(),  
    mydate <= DATE(2021,7,7),21.4,
	mydate <= DATE(2021,7,21),21.4-2,
	mydate <= DATE(2021,8,4),21.4-3,
	mydate <= DATE(2021,8,18),21.4-4,
	mydate <= DATE(2021,9,1),21.5-1,
	mydate <= DATE(2021, 9,15),21.5-2,
	mydate <= DATE(2021, 9,29), 21.5-3,
	mydate <= DATE(2021, 10,13), 21.5-4,
	mydate <= DATE(2021, 10,27), 21.6-1,
	mydate <= DATE(2021, 11,10), 21.6-2,
	mydate <= DATE(2021, 11,24), 21.6-3,
	mydate <= DATE(2021, 1,3), 21.7,
	mydate <= DATE(2022, 1,19),22.1,
	mydate <= DATE(2022, 2,02),22.2,
	mydate <= DATE(2022, 2,16),22.3,
	mydate <= DATE(2022, 3,2),22.4,
	mydate <= DATE(2022, 3,16),22.5,
	mydate <= DATE(2022, 3,30),22.6,
	mydate <= DATE(2022, 4,13),22.7,                                                                 
	mydate <= DATE(2022, 04,27),22.8,
BLANK())

     

     

    • speedramps's avatar
      speedramps
      Super User
      4 years ago

      Hi again bluranger44

       

      The [result.closed_at] is a date/time type field.

      Recommend you transform it to a date field.


      If you use Solution 1 then that will make DAX debugging easier for you.

      If you use Solution2 then you must user a date to date relationship.

      Dont use a date to date/time to relationship.

       

      See my examples, both methods do work  Click here for example solutions