Forum Discussion
PowerQuery if with multiple conditions AND OR
Should be simple
Research = if(or('Forced Receipts'[Cost Amt]>500, 'Forced Receipts'[OC Stat]="Closed","Required",""))
Doesnt work.
I want that it the Forced Receipts cost amount is mroe than $500 to print required in the field
OF
if the OC Stat is = to closed, then print required.
What i am missing in the formual?
Hello,
When you say PowerQuery, are you writing this in the PowerQuery editor in the M language, or in DAX?
By the looks of your code it appears you are adding a column in DAX (PowerQuery is the query editor).
You are missing a closing bracket in the OR statement after "Closed":
Research = IF ( OR ( 'Forced Receipts'[Cost Amt] > 500, 'Forced Receipts'[OC Stat] = "Closed" ), "Required", "" )I would reccomend using the double bar || as an OR. You can nest multple OR statements (The AND equivilent is && ). Also, you can use BLANK() instead of "":
Research = IF ( 'Forced Receipts'[Cost Amt] > 500 || 'Forced Receipts'[OC Stat] = "Closed", "Required", BLANK() )If you are in PowerQuery, it is somwhat different. In the M language, the advanced editor could look like this:
Table.AddColumn(///Last Step///, "Research", each if [Cost Amt] >500 or [OC Stat] = "Closed" then "Required" else null)
relace///Last Step///
with the name of your last step. Alternatively, you can use add conditional column.
1 Reply
Hello,
When you say PowerQuery, are you writing this in the PowerQuery editor in the M language, or in DAX?
By the looks of your code it appears you are adding a column in DAX (PowerQuery is the query editor).
You are missing a closing bracket in the OR statement after "Closed":
Research = IF ( OR ( 'Forced Receipts'[Cost Amt] > 500, 'Forced Receipts'[OC Stat] = "Closed" ), "Required", "" )I would reccomend using the double bar || as an OR. You can nest multple OR statements (The AND equivilent is && ). Also, you can use BLANK() instead of "":
Research = IF ( 'Forced Receipts'[Cost Amt] > 500 || 'Forced Receipts'[OC Stat] = "Closed", "Required", BLANK() )If you are in PowerQuery, it is somwhat different. In the M language, the advanced editor could look like this:
Table.AddColumn(///Last Step///, "Research", each if [Cost Amt] >500 or [OC Stat] = "Closed" then "Required" else null)
relace///Last Step///
with the name of your last step. Alternatively, you can use add conditional column.