Forum Discussion
Tricky IF DAX Formula
Hi Experts
I want to do the following as a either a if or Switch in a calculated column...
18+ should sum from 18 onwards to max value fine
Anonymous,
Try this calculated column:
CalculatedColumn = SWITCH ( TRUE, RTT[Actual_Weeks_Waiting] >= 78, "78+", RTT[Actual_Weeks_Waiting] >= 72, "72+", RTT[Actual_Weeks_Waiting] >= 52, "52+", RTT[Actual_Weeks_Waiting] >= 18, "18+", RTT[Actual_Weeks_Waiting] < 18, "Under 18", BLANK () )
7 Replies
Hi Anonymous,
The problem statement that you have requires you to check for a range instead of checking if it is greater than a particular number. A number like 14 will result "Under 18" but since your second condition is >18, anything that is more than 18 (25, 53, 73, 79) will statisfy that condition and you will get "18+". The rest of the conditions in your code will never even be checked.
You should write the code for checking the ranges like this:
Category = SWITCH(TRUE, 'Table'[Values]<18, "Under 18", 'Table'[Values]>=18 && 'Table'[Values]<52, "18+", 'Table'[Values]>=52 && 'Table'[Values]<72, "52+", 'Table'[Values]>=72 && 'Table'[Values]<78, "72+")Result:
Did I answer your question? Mark this post as a solution if I did!
Anonymous,
Try this calculated column:
CalculatedColumn = SWITCH ( TRUE, RTT[Actual_Weeks_Waiting] >= 78, "78+", RTT[Actual_Weeks_Waiting] >= 72, "72+", RTT[Actual_Weeks_Waiting] >= 52, "52+", RTT[Actual_Weeks_Waiting] >= 18, "18+", RTT[Actual_Weeks_Waiting] < 18, "Under 18", BLANK () )Anonymous,
Glad to hear it worked. By the way, you can remove the BLANK() argument since it will return BLANK if none of the conditions are met.
could you shed some light on the following
link: https://community.powerbi.com/t5/Desktop/Switch-Measure-error/m-p/2825247#M976120
Anonymous,
I looked at your other post and noticed a missing close parenthesis at the end.
