Ravindra BagaleCourses & study guides

13. DAX: Data Analysis Expressions

13.7 Logical Functions

By Last updated:

IF

Delivery Target Status =
IF([% Orders Delivered Under 10 Mins] >= 0.8, "On Target", "Below Target")

-- Calculated column in Orders
Has Discount = IF(Orders[Discount] > 0, "Yes", "No")

If the third argument is omitted, IF returns BLANK when the condition is false. (The 80% target above is only an example value.)

AND, OR, NOT

AND() and OR() accept exactly two arguments; for more conditions use the operators && and ||.

-- Calculated columns in Orders
Late Cash Order =
IF(AND(Orders[Delivery Time Mins] > 20, Orders[Payment Mode] = "Cash on Delivery"), "Yes", "No")

Big Basket Flag = IF(Orders[Quantity] >= 5 || Orders[Amount] >= 500, "Big", "Normal")

SWITCH

SWITCH replaces long nested IFs.

-- Simple form: compare one expression to values
Payment Group = SWITCH(Orders[Payment Mode],
    "UPI", "Digital",
    "Card", "Digital",
    "Wallet", "Digital",
    "Cash on Delivery", "Cash",
    "Other")

-- SWITCH(TRUE()) pattern: first condition that is TRUE wins
Delivery Band = SWITCH(TRUE(),
    ISBLANK(Orders[Delivery Time Mins]), "Not delivered",
    Orders[Delivery Time Mins] <= 10, "Within 10 min",
    Orders[Delivery Time Mins] <= 20, "11-20 min",
    "Over 20 min")

-- Time-of-day slot from the Order Hour column
Day Part = SWITCH(TRUE(),
    Orders[Order Hour] < 6, "Late Night",
    Orders[Order Hour] < 12, "Morning",
    Orders[Order Hour] < 17, "Afternoon",
    Orders[Order Hour] < 21, "Evening",
    "Night")

DIVIDE

Lakshat theva, always use DIVIDE for division in measures. It handles division by zero safely and returns BLANK (or an alternate result) instead of an error.

AOV (Average Order Value) = DIVIDE([Total Sales], [Delivered Orders])

Discount % = DIVIDE([Total Discount], [Total Sales] + [Total Discount], 0)

Ravindra Bagale's Tip

Friends, many students write [Total Sales] / [Total Orders] and get errors or infinity when a city has no orders. Use DIVIDE(numerator, denominator), which returns blank (or an alternate value) when the denominator is zero. Prefer SWITCH(TRUE(), …) over deeply nested IFs for readability. Never forget this.