Welcome to Excel Jet Consult blog post. In this tutorial, we will how to use IF & OR functions in Power Query and Power BI DAX. Let’s get started
DEFINITION OF TERMS
IF Function evaluates whether a condition is satisfied and returns one value if TRUE and another value if FALSE
IF Syntax =IF(LogicalTest, ResultIfTrue, [ResultIfFalse])
OR Function evaluates whether any of the arguments are TRUE and return TRUE or FALSE
OR Syntax =OR(logical1, [logical2],…)
Power Query Method
In the caption below, we have our data loaded to the Power Query. In this example, we want to give 13% Bonus to Customers who either have Total Purchase that is greater than or equal to 20,000 or has 3 or more stores
- In the General group of the Add Column tab, click on Custom Column
- In the Custom Column dialogue box, provide Bonus as the New Column Name (optional)
- In the Custom Column Formula, execute the formula:
=if [Total Purchase]>=20000 or [#”Stores >=3?”]>=3 then [Total Purchase]*0.13 else 0
What the above formula is checking is that if Total Purchase is greater than or equal to 20000 or Stores is greater than or equal to 3, if true, then take the Total Purchase and multiplied by 0.13. If false, give 0
Note that the logical functions must be in lower case all through i.e if, or, then and else
- Click OK.
From the caption above, we noticed that Customer in row 9,11, and 14 got 0 because, their individual Total Purchase was not up to 20,000 neither is their Stores up to 3. They did not meet any of the two specified criteria
Furthermore, in row 1, though the customer Total Purchase was below 20,000 thresholds but because he operates more than 3 stores, he is entitled to the 13% bonus based on this Total Purchase. The reverse is the case for Customer in rows 4, 5, 6.
Power BI DAX Method
We have the same data loaded to the data model. In this example, we want to give 13% Bonus to Customers who either have Total Purchase that is greater than or equal to 20,000 or has 3 or more stores (same question with Power Query)
- In the Calculations group of the Modelling tab, click on New Column
- In the formula bar, execute the formula: PBI Bonus = IF(OR(Data[Total Purchase]>=20000,Data[Stores >=3?]>=3),Data[Total Purchase]*0.13,0)
- Click Enter
When we compared the Power BI formula with the Power Query formula, we notice slight variation in both. However, they are doing the same thing
