cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

## 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
Super User

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

Proud to be a Super User!

7 REPLIES 7
Memorable Member

Hi @Checkers111,

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!

Super User

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

Proud to be a Super User!

Helper III

could you shed some light on the following

Super User

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

Proud to be a Super User!

Helper III

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

Helper III

Excellent sir worked.......

Super User

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.

Proud to be a Super User!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors