Forum Discussion
How to Create Measure for Multiple Criteria in Power Query
Hello, I need help creating the following measures in Power Query and Power BI separately: Measure If the “Cost variance” is less than or equal to -$10,000 and is -10% or Greater, retur...
Column =
SWITCH (
TRUE (),
'Table'[Cost Variance] <= -10000 && 'Table'[% Change] <= -.1, "Under Budget",
'Table'[Cost Variance] >= 10001 && 'Table'[% Change] >= .1, "Over Budget",
'Table'[Cost Variance] >= 9999 && 'Table'[Cost Variance] <=1000, "On Target",
BLANK ()
)
Measure =
SWITCH (
TRUE (),
MIN ( 'Table'[Cost Variance] ) <= -10000 && MIN ( 'Table'[% Change] ) <= -.1, "Under Budget",
MIN ( 'Table'[Cost Variance] ) >= 10001 && MIN ( 'Table'[% Change] ) >= .1, "Over Budget",
MIN ( 'Table'[Cost Variance] ) >= 9999 && MIN ( 'Table'[Cost Variance] ) <=1000, "On Target",
BLANK ()
)Hope this helps.
Good Luck! :smileyhappy:
Thanks for the quick response. I just want to make sure am doing this correctly because it's showing an error message
The above was DAX
In the Query Editor the formula would be...
= if [Cost Variance] <= -10000 and [#"% Change"] <= -.1 then "Under Budget"
else if [Cost Variance] >= 10001 and [#"% Change"] >= .1 then "Over Budget"
else if [Cost Variance] >= 9999 and [Cost Variance] <= 10000 then "On Target"
else null
It worked everywhere except where the % Change field = "null". How can I correct this error?
