Multiple Conditions/Ifs and Calculated Columns

[Terp]

I am trying to clean up some data, but still don't have a strong command of DAX (work-in-progress). 🙂

In short, they have a mix of 'regions' and 'countries' and some duplicative aggregates (e.g., overseas, totals, "(Air Only)", etc), so thought it was best to create a calculated column and assign a value based on other columns.

They include the 'World Region' for all countries; a blank cell would indicate it's either a region or one of those wonky aggregates/special conditions.

I first created a column that was simply—if 'World Region' is blank = "Region", else "Country"...but then have to control for the other junk I found in there, so I want to add a few more conditions to my simple "Is Country?" calculated column.

Trying to get fancy with limited DAX, I tried two ways, neither of which worked:

Is Country? = if('Annual Arrivals'[World Region] ="","Region",if('Annual Arrivals'[Name of Country/Region] ="TOTAL ALL COUNTRIES","Aggregate","Country"))

...then:

Is Country? = if('Annual Arrivals'[World Region] ="","Region",if(CONTAINSSTRING('Annual Arrivals'[Name of Country/Region],"TOTAL"),"Aggregate","Country"))

My first conditon works (if world region is blank, it's a region" and my 'else' works (if world region is not blank, it's a country)...just can't add an expression to the calculated column that says—if 'Name of Country/Region" is "Total All Countries" (or constains (Air-Only), call it something different, if that makes sense. 🙂

Maybe a 'Switch' function is the proper way to do this or do I have poor syntax/structure of my compounded 'ifs'? 🙂

 

[Terp]

...added 4 or 5 conditions and learned the order of operation matters, too. Nice. Always impressed with the simplicity and power under the hood. That was too easy doing it in PQ, sir. Good call. 🙂

[davehus]

It's very good and the syntax for and/or is easy to understand. 

[davehus · Accepted solution]

Hi @Terp, I'd suggest creating this process in Powerquery using a conditional column. It has an easier logic to it and is preprocessed as opposed to being a calculated Dax column. 

[Terp]

Thank you, sir...never did one using PowerQuery, but after adding a few conditions (and figuring out how to address the null), it indeed worked well!

...will work on my DAX to figure out the compounding thing, but this definately solves many problems.  Thanks again.

[davehus]

No problem, I find doing a lot of this upstream in the etl stage or back at the source if possible. It's best all round for report performance. 😊