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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DerMainzer
Resolver I
Resolver I

two or more IF conditions

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

 

 

1 ACCEPTED SOLUTION
DerMainzer
Resolver I
Resolver I

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

DerMainzer_0-1676006995257.png

 

View solution in original post

6 REPLIES 6
DerMainzer
Resolver I
Resolver I

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

DerMainzer_0-1676006995257.png

 

JirkaZ
Solution Specialist
Solution Specialist

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")

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):

DerMainzer_0-1676004013682.png

 

If I add a new measure it tells me:

DerMainzer_1-1676004153723.png

 

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

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"
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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:

DerMainzer_2-1676005038800.png

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
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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