Solved: Switch True Statement Help Needed

Anonymous · ‎04-22-2022

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?

Sprintclosedat2 = Switch(True(),
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,6,23), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 7,7)),21.4,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,7,07), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 7,21)),21.4-2,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,7,21), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 8,4)),21.4-3,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,8,4), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 8,18)),21.4-4,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,8,18), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 9,1)),21.5-1,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,9,1), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 9,15)),21.5-2,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,9,15), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 9,29)), 21.5-3,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,9,29), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 10,13)), 21.5-4,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,10,13), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 10,27)), 21.6-1,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,10,27), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 11,10)), 21.6-2,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,11,10), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 11,24)), 21.6-3,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,12,08), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2021, 1,3)), 21.7,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2022,1,03), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2022, 1,19)),22.1,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2022,1,19), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2022, 2,02)),22.2,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2022,2,02), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2022, 2,16)),22.3,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2022,2,16), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2022, 3,2)),22.4,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2022,3,02), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2022, 3,16)),22.5,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2022,3,16), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2022, 3,30)),22.6,
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2022,3,30), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2022, 4,13)),22.7,                                                                 
	AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2022,04,13), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] <= DATE(2022, 04,27)),22.8)

M

speedramps · ‎04-23-2022

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

View solution in original post

speedramps · ‎04-23-2022

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 · ‎04-23-2022

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 · ‎04-23-2022

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

PhilipTreacy · ‎04-22-2022

Hi @Anonymous

What do you mean its not picking up all the AND statements? What are you expecting to happen and what is it actually doing?

Looking at your code, it seems syntactically ok but when you are trying to to create bands like this, the upper and lower limits need to be separate, not overlapping.

What I mean is that when you want to check closed tasks between 23 Jun 21 and 7 Jul 21, then the next range shouldn't start on 7 Jul 21. The end of one range is overlapping the start of the next.

So your code should look something like

AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,6,23), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] < DATE(2021, 7,7)),21.4,
AND('HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] >= Date(2021,7,07), 'HX/Silvis ALL Tasks Closed'[result.closed_at].[Date] < DATE(2021, 7,21)),21.4-2,

etc.

Regards

Phil

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin

Proud to be a Super User!

Anonymous · ‎04-23-2022

Thanks for the response @PhilipTreacy , I think that is exactly how my data is formatted.

I am attempting to get the specific sprint number attached to the correct ticket number based on the date the incident was closed or resolved. Catergorize the ticket based on the sprint it was closed.

The code works for a large chuck as you can see in the screenshot below (22.1 - 22.8), but for some reason its not working for the additional lines.

speedramps · ‎04-23-2022

Hi again Blueranger

Have you tried my 2 solutions.

For sprint dates it is best pratice to

create a Calander with a Date and Sprint.

Then create a 1:m relationship between the Calander[Date] and your table[Date].
This solution is simplex and quicker than a DAX formula because a user can change the calandar without progarming DAX

Click here for example solutions