two or more IF conditions

[DerMainzer]

Hi

I'm an absolute Noob and just started learning so please excuse me if my search through the different articles didn't bring me the solution.

I don't get the IF AND   or   IF && syntax, Neither as Meassure nor in Power Query - and I want to learn both.

I have a table ( let's called it PERSONS ) with a row ( let's call it AGE ). Within are different numbers between 0 - 50.

I want to ad a new row: it should check if the value of the first row is >=10 and <=20 and then write into each coloum "BETWEEN 10-20", is it >20 and <=30 "BETWEEN2 20-30"  ..... and a few more

The AND is important, since I have more tasks that need to be connected

I would really appreciate if someone could help me with both: Power Quera and New Meassure.

Thanks so much in advance,

Tim

[DerMainzer · Accepted solution]

EDIT: in Power Query I've made it now that way as an example:

[JirkaZ]

Hi Tim, 

let's start with basics - Power Query and DAX are two different languages and thus have a different syntax. 

This is the M query reference (Power Query): Operators - PowerQuery M | Microsoft Learn for all the various operators. As you can see the logical and is just "and"

For DAX the reference is here: DAX operators - DAX | Microsoft Learn and you can see that the logical and has a syntax of "&&"

Also in your problem description I think you're mixing up rows with columns. 

The correct syntax in your case would be: 

My New Column = SWITCH(TRUE(), 
'MyTable'[Age] > 10 && 'MyTable'[Age] <= 20, "Between 10-20", 
'MyTable'[Age] > 20 && 'MyTable'[Age] <= 30, "Between 20-30", 
"Other")

[DerMainzer]

Hey,

thanks for the quick response. You're right, I need to get to the basics, I've just started to learn Power BI and I'm on my own. I have an offer for a project in 3-4 months and if I tell them I'm not able to they'll then take another one, but that's another story !

UnfortunatelyI can't make your solution work: This is the table (I changed it to fit your code):

If I add a new measure it tells me:

Cannot find a single value for column 'Age' in table 'MyTable'. This can happen when a MEasure formula refers to a column with many values ​​without an aggregation like MIN, MAX, Count...

Regards,

Tim

[Jihwan_Kim]

Hi, 

I am not sure if I understood your question correctly, but please try something like below.

It is for creating a calculated column.

Expected result Calculated Column: =
SWITCH (
    TRUE (),
    Persons[Age] > 10
        && Persons[Age] <= 20, "BETWEEN 10-20",
    Persons[Age] > 20
        && Persons[Age] <= 30, "BETWEEN 20-30",
    Persons[Age] > 30
        && Persons[Age] <= 40, "BETWEEN 30-40"
)

[DerMainzer]

Hi, 

thanks for the quick response but I get the same error as above. Let me try to explain it better:

My goal is to be able to add a new coloumn via Power Query OR via a measure! I'm in the learning phase and just started with power BI.

Power Query: I want to add a custom coloum to my table:

But PQ doesn't know what SWITCH is!

Also I want to be able to add a new coloumn via a meassure but I think in the statement is something missing lik ADDCOLOUMN.

Sorry, I can't explain it better.

Regards,

Tim

[JirkaZ]

The SWITCH() function is a DAX function - therefore will not work in Power Query, but it will work when creating a calculated column in the report engine. 

If you want to use Power Query, then you'll have to use multiple embedded if then else statements