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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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