Multiple IF Statements

What is the appropriate to create a new column in the data that has multiple IF statements. Below is what I started but I think I am missing something. I am trying to use nested IF statements. I know how to do it when dealing with less than or equal to but in the case where its equal and there is no false I am new to that and likely over thinking it.

Based on the Academic Year in the Academic Year Column I am trying to sign a specific number, which is the Cost of Instruction.

In case anyone starts looking at this I am still playing with it I just need to get the ELSE syntax correct for DAX.

SOLVED! This is the formula I needed to use. Then after that I figured out that I could have just used a conditional column. I racked my brain for nothing!!! :tired_face::tired_face::tired_face:

= Table.AddColumn(#"Reordered Columns2", "Instructional Cost", each if [Academic Year] = "2012-13" then 304 else if [Academic Year] = "2013-14" then 309 else if [Academic Year] = "2014-15" then 313 else if [Academic Year] = "2015-16" then 299 else if [Academic Year] = "2016-17" then 305 else if [Academic Year] = "2017-18" then 300 else 300)

I would use SWITCH/ True() logic instead of nested ifs. It’s a better way to write this

See below

1 Like

Hi, quick question…are you able to combine arguments with text and integers eg:
SWITCH( TRUE(
status = “Quote” && probability >= 75, “High probability of winning quote”
)

Figured it out! For anyone who is interested:

Current Status = 
SWITCH (
    TRUE (),
    'Table'[SALESSTATUS] = "New" && 'Table'[SALES_STAGE] = "Design", "In Detailed Design",
    'Table'[SALESSTATUS] = "New" && 'Table'[SALES_STAGE] = "", "In Design",
    'Table'[SALESSTATUS] = "Quote" && 'Table'[PROBABILITY] < 75, "Quote - Possible To Win",
    'Table'[SALESSTATUS] = "Quote" && 'Table'[PROBABILITY] >= 75, "Quote - Likely To Win",
    'Table'[SALESSTATUS] = "In Progress" && 'Table'[SALES_STAGE] = "Won", "Won - Scheduled",
    'Table'[SALESSTATUS] = "In Progress" && 'Table'[SALES_STAGE] = "Design", "Won - To Be Detailed",
    'Table'[SALESSTATUS] = "In Progress" && 'Table'[SALES_STAGE] = "Quote", "Won - Waiting On Drawings",
    "On Hold"
)

Nice one

1 Like

Would anyone happen to know how to do incorporate a “does not contain” field in a Switch/True statement?

I would like to filter out any dashes (aka “-”) in a product string and this is the only piece I can’t figure out.
How you can you write “does no contain dashes” in DAX language?

@tomtom62,

Try

CONTAINSSTRING(<within_text>, “-”) = FALSE

where within_text is the text to be evaluated.

https://docs.microsoft.com/en-us/dax/containsstring-function-dax

https://dax.guide/containsstring/

  • Brian

It worked! Thank you @BrianJ !

Similar question… but what if I want to use DAX to filter out the product nomenclature that “does not start with R”?

That gets me thinking…Is there some type of resource where I can find all of the ways to to filter using advanced filtering that you can do by clicking in the report view of PowerBI, but using DAX expressions only?

(Does not contain, is not blank, is not, etc.)

@tomtom62,

For that one, you’d use:

LEFT( text, 1) <> “R”

where text is the field you want to evaluate

My go-to resource for this type of thing is dax.guide

If you go there, and click on Groups, the two groups of functions you’re looking for are “Information” and “Text”.

Hope this is helpful.

  • Brian
2 Likes

To the contributors of this post, thank you for all your inputs for this topic to be solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!