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.
This has been tough for me to figure out. I think it stems from the differences in DAX vs whatever is used in the Query editor. I need to create a custom column in the query editor that uses multiple Ifs. This is what I have written but I know the syntax is not correct. I do not think the conditional column will allow me to add all the conditions I need, so I prefer to learn how to do this with a custom column if statement
if([Cost_Centers.Cost_Center]=OR(109,6044,6045,6046,6047,6048,7020,7021,7022,7023,7024,7025,7026,7027,7028,7029,7030,7031,7032,7033,7034),
"Coffee",
if([Cost_Centers.Cost_Center]=OR(7001,7002), "Tea",
if([Cost_Centers.Cost_Center]=OR(1,40,91,93,94,129,150,217,228,287,291,349,364,381,407,436,477,479,500,501,530,534,537,556,558,566,583,588,591,597,623,629,710,778,779,780,781,782,783,784,785,786,787,789,790,791,792,793,794,795,796,798,801,809,810,815,877,878,881,901,1609,1995,7009,7010)
,"Soda")))
Using M, I would put the lists in a variable. This would come in the advanced editor, After the first line "Let", and before your first step, probably "Source"
Let CoffeeList =({109,6044,6045,6046,6047,6048,7020,7021,7022,7023,7024,7025,7026,7027,7028,7029,7030,7031,7032,7033,7034}), TeaList =({7001,7002}), SodaList = ({1,40,91,93,94,129,150,217,228,287,291,349,364,381,407,436,477,479,500,501,530,534,537,556,558,566,583,588,591,597,623,629,710,778,779,780,781,782,783,784,785,786,787,789,790,791,792,793,794,795,796,798,801,809,810,815,877,878,881,901,1609,1995,7009,7010}),
You can then use List.Contains to check if it is in that list. The first argument is the list, the second is the value.
The if statement needs to start with "each if", the seperated by "else if", and finally an "else" for other values like:
#"New If Column" = Table.AddColumn(#"STEP", "Column Name", each if List.Contains([Cost_Centers.Cost_Center], CoffeeList then "Coffee" else if List.Contains([Cost_Centers.Cost_Center], TeaList then "Tea" else if List.Contains([Cost_Centers.Cost_Center], SodaList then "Soda" else "Other" ),
Replacing
#"STEP"
with the name of the previous step
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |