cancel
Showing results 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

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.

Tim

1 ACCEPTED SOLUTION
Resolver I

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

6 REPLIES 6
Resolver I

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

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

Resolver I

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

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.

Resolver I

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

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

Announcements

#### 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.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors