Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Tricky IF DAX Formula

Hi Experts

 

I want to do the following as a either a if or Switch in a calculated column...

 

If (RTT[Actual_Weeks_Waiting] < 18, "Under 18",
If (RTT[Actual_Weeks_Waiting] >=18, "18+",
If (RTT[Actual_Weeks_Waiting] >=52, "52+",
If (RTT[Actual_Weeks_Waiting] >=72, "72+",
If (RTT[Actual_Weeks_Waiting] >=72, "78+",blank())))))
 
the above only returns back Under 18 and 18+
18+ should sum from 18 onwards to max value fine
52+ should some only values from 52 onwards to max and so on
1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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 ()
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Shaurya
Memorable Member
Memorable Member

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:

 

Screenshot 2022-10-07 182212.jpg

 

Did I answer your question? Mark this post as a solution if I did!

DataInsights
Super User
Super User

@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 ()
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@Anonymous,

 

I looked at your other post and noticed a missing close parenthesis at the end.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi - even with that corrected its still not returning back the correct result

Anonymous
Not applicable

Excellent sir worked.......

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.