Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
EDIT: in Power Query I've made it now that way as an example:
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):
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |