PowerBI DAX – SWITCH

Switch is a DAX function that is used frequently to create custom groups and classifications. In this example we use Switch to create a column called approval where we code different types of approval for the total sum of Amount. You can use switch to replace multiple values with another value instead of using IF statement and replace.

The below DAX function shows how we create the New Measure called Approval

Approval = 
SWITCH(
   TRUE(),
   SUM('Fact Order'[Total Including Tax])>1000000, "Skip lvl",
   SUM('Fact Order'[Total Including Tax])>100000, "Self Approval",
   SUM('Fact Order'[Total Including Tax])>10000, "Auto Approved",
   SUM('Fact Order'[Total Including Tax])>1000, "No Approval Required",
"Investigate")

The result of the above Measure is shown in the screenshot below:-

The SQL equivalent of the above DAX function would be similar to the case statement in the below query

SELECT [Calendar Year]     
    ,[Calendar Month Number]     
    ,sum(Quantity)     
    ,'Approval' = CASE 
        WHEN sum(Quantity) > 100000
            THEN 'Skil lvl'                     
        WHEN sum(Quantity) > 10000
            THEN 'Self Approval'                     
        WHEN sum(Quantity) > 1000
            THEN 'Auto Approved'                     
        ELSE 'Investiagte'                     
        END
FROM fact.[order] o
INNER JOIN Dimension.[Date] d ON d.DATE = o.[Order Date Key]
WHERE Quantity > 100
GROUP BY [Calendar Year]     
    ,[Calendar Month Number]

Please Consider Subscribing

Subscribe