**A Power BI Technique Mined from the Power Pivot Archives**

**Below, you will find one of our all-time favorite Power BI techniques. ** In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.

**Years ago, we first wrote up this technique in the context of Power Pivot** – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”

**Since the two products share the same brains (DAX and M), this technique is more relevant today than ever.** Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!

Guest Post by Colin Banfield [**LinkedIn**]

Way back during the period of the first CTP of Gemini (which later became PowerPivot), I was working with a data set that included a column of month numbers. I wanted to create a calculated column with month names, and the only solution that I could think of at the time was using the IF function in DAX:

=IF([MonthNum]=1, “January”,

IF([MonthNum]=2, “February”,

IF([MonthNum]=3, “March”,

IF([MonthNum]=4, “April”,

IF([MonthNum]=5, “May”,

IF([MonthNum]=6, “June”,

IF([MonthNum]=7, “July”,

IF([MonthNum]=8, “August”,

IF([MonthNum]=9, “September”,

IF([MonthNum]=10, “October”,

IF([MonthNum]=11, “November”,

IF([MonthNum]=12, “December”,

)

)

)

)

)

)

)

)

)

)

)

)

This a horrendous formula, with eleven nested IF functions and a long tail of closing parentheses. I promptly made a suggestion in the Connect forum for the addition of a “Case” function in DAX, siting the preceding formula as justification for its inclusion. Sometime later, I received a message from Howie Dickerman indicating that some form of Case function was being considered for PowerPivot V2 (the current version). This “Case” function turned out to be the SWITCH() function. “Switch” is a function familiar to Access and VBA users, so it made sense to stick with that name for the DAX implementation.

### Anatomy of the SWITCH function

There are actually two usages of the SWITCH function. The first usage is equivalent to the SQL **simple Case** statement, and is the officially DAX documented usage. The second, and more powerful usage is equivalent to the SQL **searched Case** statement. The structure of the first usage is thus:

SWITCH(*expression*,

*value1, result1*,

*value2, result2*,

:

:

*else*

)

*expression* is any DAX expression that returns a single scalar value (number, string, or date), where the expression is to be evaluated multiple times (for each row/context).

*value1* is a constant value to be matched with the evaluated result of *expression*.

*result1* is any scalar expression (i.e. one that returns a scalar value) to be evaluated if the results of *expression* match the corresponding *value1*.

*else* is any scalar expression to be evaluated if the result of expression doesn’t match any of the value arguments

*expression, value1, and result1* are the only mandatory parameters in SWITCH. If the formula contains multiple value/result pairs, then the data types of *result1, result2…resultn* must be the same. In addition, if you include an *else* expression, the expression must evaluate to the same data type as *result*.

**Note:** If the result of *expression* does not match any listed *value*, and the *else* condition is omitted, SWITCH returns an error.

Using SWITCH instead of IF in the formula provided at the top of this post, we get:

=SWITCH([MonthNum],

1,”January”,

2,”February”,

3,”March”,

4,”April”,

5,”May”,

6,”June”,

7,”July”,

8,”August”,

9,”September”,

10,”October”,

11,”November”,

12,”December”,

“Invalid Month Number”

)

The SWITCH formula is much cleaner than the IF formula – you don’t have to repeat [MonthNum] in every condition tested, and there is a single closing parenthesis. Incidentally, this particular example is used in the description of the SWITCH function in the official DAX documentation.

In reality though, when faced with a scenario where both *value* and *result* are constants, a table-based solution is most often the best approach. In Gemini, I ended up creating a table linked to a MonthNum/MonthName lookup table in Excel.

An example of using SWITCH where a table approach is not possible (because of the dynamic nature of the expressions and calculations involved) would be a formula like the following:

=IF(COUNTROWS(VALUES(DimPeriod[Period]))=1,

IF(VALUES(DimPeriod[Period]) = “Current”, [Sales],

IF(VALUES(DimPeriod[Period]) = “MTD”, [Sales](DATESMTD(DimDate[Datekey])),

IF(VALUES(DimPeriod[Period]) = “QTD”, [Sales](DATESQTD(DimDate[Datekey])),

IF(VALUES(DimPeriod[Period]) = “YTD”, [Sales](DATESYTD(DimDate[Datekey])),

IF(VALUES(DimPeriod[Period]) = “LastYear”, [Sales](DATEADD(DimDate[Datekey],-1,YEAR)),

IF(VALUES(DimPeriod[Period]) = “PriorYearMTD”, [Sales](DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR)),

IF(VALUES(DimPeriod[Period]) = “PriorYearQTD”, [Sales](DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR)),

IF(VALUES(DimPeriod[Period]) = “PriorYearYTD”, [Sales](DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR)),BLANK()

)

)

)

)

)

)

)

),

[Sales]
)

The above is a formula you can find in the excellent whitepaper titled **DAX in the BI Tabular Model**, written by Howie Dickerman et al. The formula is based on version 1 of the whitepaper (before SWITCH existed), and wasn’t updated to reflect new alternative functions in PowerPivot V2. This is an awkward and error-prone formula to write, because you have to be careful about including the correct number of closing parentheses before the [Sales] measure (the false condition of IF(COUNTROWS…).

Now consider how this formula would be rewritten using SWITCH:

=IF(HASONEVALUE(DimPeriod[Period]),

SWITCH(VALUES(DimPeriod[Period]),

“Current”, [Sales],

“MTD”, [Sales](DATESMTD(DimDate[Datekey])),

“QTD”, [Sales](DATESQTD(DimDate[Datekey])),

“YTD”, [Sales](DATESYTD(DimDate[Datekey])),

“LastYear”, [Sales](DATEADD(DimDate[Datekey],-1,YEAR)),

“PriorYearMTD”, [Sales](DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR)),

“PriorYearQTD”, [Sales](DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR)),

“PriorYearYTD”, [Sales](DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR)),

BLANK()

),

[Sales]
)

The SWITCH version of the formula is easier to write and less error prone. SWITCH also replaces* eight IF function calls with a single function call*. Note that I’ve substituted COUNTROWS(VALUES(DimPeriod[Period]))=1 with HASONEVALUE(DimPeriod[Period]). HASONEVALUE is new in PowerPivot V2, and eliminates the extra function call in the IF(COUNTROWS(VALUES…) test.

The second usage of the SWITCH function has the following structure:

SWITCH(**TRUE()**,

*booleanexpression1, result1,
booleanexpression2, result2,
* :

:

*else*

)

where TRUE() is a DAX function, and *booleanexpression1,**booleanexpression2,…*are any valid Boolean expressions (i.e. returns True or False). The first *booleanexpression* that evaluates to True will return the corresponding *result* as the formula result.

Consider the next example, which uses the IF function:

=IF(ISFILTERED(DimAccount[Level6]),6,

IF(ISFILTERED(DimAccount[Level5]),5,

IF(ISFILTERED(DimAccount[Level4]),4,

IF(ISFILTERED(DimAccount[Level3]),3,

IF(ISFILTERED(DimAccount[Level2]),2,

IF(ISFILTERED(DimAccount[Level1]),1

)

)

)

)

)

)

In the above formula, we are evaluating six different Boolean expressions, using ISFILTERED. The formula was inspired by a couple of breathtaking articles written by an Italian BI guru with the same name as a famous Italian carmaker (hint…its *not* Lamborghini or Alfa Romeo).

If at this point you’ve given up wondering who I’m referring to, please see **Parent/Child Hierarchies in Tabular with Denali** and **Clever Hierarchy Handling in DAX**.

We can rewrite the formula using SWITCH as follows:

=SWITCH(**TRUE()**,

ISFILTERED(DimAccount[Level6]),6,

ISFILTERED(DimAccount[Level5]),5,

ISFILTERED(DimAccount[Level4]),4,

ISFILTERED(DimAccount[Level3]),3,

ISFILTERED(DimAccount[Level2]),2,

ISFILTERED(DimAccount[Level1]),1

)

In the above formula, we’ve eliminated five closing parentheses and replaced six IF functions with a single SWITCH function.

We can use SWITCH in cases where we need to evaluate inequalities. For example, consider a community foundation that accepts monetary gifts from donors and invests the proceeds in a series of endowment funds. For a given month, the foundation wants a report that provides the quantity and fund balances for funds of given sizes. In the database table that provides the fund name (and/or ID) and balances, we can create a calculated column named Fund Size, e.g.:

Fund Size:=SWITCH(TRUE(),

AND([Fund Balance]>=0, [Fund Balance]<=10000), “Up to $10,000”,

AND([Fund Balance]>=10001, [Fund Balance]<=50000), “$10,001 to 50,000”,

AND([Fund Balance]>=50001, [Fund Balance]<=100000), “$50,001 to 100,000”,

AND([Fund Balance]>=100001, [Fund Balance]<=500000), “$100,001 to 500,000”,

AND([Fund Balance]>=1500001, [Fund Balance]<=1000000), “$500,001 to 1,000,000”,

“greater than $1,000,000”

)

We can then put Fund Size in the Row area of a PivotTable, and use Fund Balance in measures that calculate the total quantity and total balances of the various fund sizes. Come to think of it, the preceding formula is nothing more than another example of **banding**. In this instance, I would opt for the table solution, but there could be instances where the complexity of the Boolean or result expressions might resist a table approach.

### Summary

Advantages of SWITCH() over IF():

- Formulas are easier to write, and subsequently read
- Formulas are less error-prone, and thus easier to debug
- A large number of IF calls may, or may not have a negative performance impact in large datasets (I haven’t done any testing to prove or disprove this statement)

Advantages of IF() over SWITCH():

- None

What a cleaver trick using SWITCH( TRUE(), … ) !!!

Nice hint, Rob!

Wasn’t my trick, it was Colin’s 🙂

Great post on the switch formula. Very helpful indeed.

In your purposefully overly simple example, I might use a linked look up decode table and link it back to your main table. In fact, with more than 3 look ups, I’ll always use a linked look up table

Struggling a lot with data sets myself :-), want to share this tip (maybe somebody find it helpful):

Instead of using IF/SWITCH, to return the name of the month having Date:

– in Excel: =TEXT(,”MMMM”)

– in PowerPivot: =FORMAT(,”MMMM”)

Having month number:

– in Excel: =TEXT(*30,”MMMM”)

– in PowerPivot: =FORMAT(*30,”MMMM”)

Sorry, 1st parameter in formulas got dissapeared, here it is:

Instead of using IF/SWITCH, to return the name of the month having Date:

– in Excel: =TEXT(Link to sell with Date,”MMMM”)

– in PowerPivot: =FORMAT(Link to sell with Date,”MMMM”)

Having month number:

– in Excel: =TEXT(Link to sell with Month# *30,”MMMM”)

– in PowerPivot: =FORMAT(Link to sell with Month# *30,”MMMM”)

Hi Yuri,

Great Tip! Thanks.

Awesome, this really helped me through some trouble also. Great Tip, Thanks.

This is brilliant! I was trying to construct a CASE statement so I Googled “powerpivot case statement” and this article was the first hit. Without the article, I never would have known that you could use SWITCH in this way.

Here is the simple category formula that I was able to write using this method:

=SWITCH(TRUE(),

FactInternetSales[UnitPrice]<50,"Under $50",

FactInternetSales[UnitPrice]<=500,"$50 – $500",

FactInternetSales[UnitPrice]5000,”> $5,001″

)

How come PowerQuery doesn’t recognize switch (tried upper and lower case): Expression error: The name ‘switch’ wasn’t recognized. Make sure it’s spelled correctly.

“How come PowerQuery doesn’t recognize switch…”

Because it’s not a supported keyword or function in Power Query. I suggest that you read the documentation.

I used this method to segment some results into buckets. My formula is similar to the one above,

=SWITCH(TRUE(),

FactInternetSales[UnitPrice]<50,"Under $50",

FactInternetSales[UnitPrice] $5,001″

)

So now I can label each row with a bucket. What I want to do next is use the buckets to get counts. How many rows in bucket 1, how many in bucket 2, etc. When I try to place the Bucket measure in a Pivot Table row or column, Excel 2016 doesn’t allow it.

Ever found a solution for this? Have identical issue using switch.

This sample doesn’t seem complete. Maybe something went wrong with copy and paste. Maybe DAX parses the entire switch rather than dumping out when it hits a true. Have you tried to use the AND like in the sample above to make sure that every row ONLY matches 1 group max? I don’t have 2016 installed yet (rebelling against the install everything as only option), so I can’t check things yet on 2016.

I used this method to segment. i got error “more than one row was detected when parsing column reference”

Hi,

How to use switch to compare time like, 9AM – 2PM i want Shift 1, for 2Pm – 11PM, I want shift 2, for 11Pm- 9AM I want Shift 3.

Twinkle – Although there are many ways, this is probably the most basic. First, make a calculated column for yourself with hours only in a 24 hour format instead of am/pm. Second, use the final SWITCH sample from the article that uses the SWITCH(TRUE() …) You need to decide if you want 9am in Shift 1 or 3 and use your greater than and less than signs to make it happen the way you want.

Hi,

I am creating dashboard in power BI desktop application and using the bellow syntax in my chart’s column:

=SWITCH([MonthNum],

1,”January”,

2,”February”,

3,”March”,

4,”April”,

5,”May”,

6,”June”,

7,”July”,

8,”August”,

9,”September”,

10,”October”,

11,”November”,

12,”December”,

“Invalid Month Number”

)

this is working fine up to condition 10, but as i’m adding 11th condition which is for November the chart is showing an error “case statement can only be nested to level 10”.

I have tried “if, switch, case” but for all it is not allowing to add conditions more than 10.

Shiwani,

I ran into the same issue There was an error in the SWITCH implementation in Power BI, but it has been fixed in a release earlier this year. If you update your powerBI desktop, that function will work properly.

Colin

Hi,

I’m banging my head against the wall in search of a solution to a-what seems simple query. I’d like to find the networkdays/business days between two dates. I pose the question as a switch statement. I just want to receive an output of “0” in the beginning date is empty. And if the ending date is empty, calculate the business days from that date and Today(). Otherwise, just count the networkdays/business days between the beginning date and the end date.

SWITCH (TRUE()

[TextStringDateColumnA] = “”, 0,

AND [TextStringDateColumnD] = “”,

CALCULATE (SUM(DateTable[NumberStringWORKDAY]), DATESBETWEEN (DateTable[TextStringDate], AND’SharePointList'[TextStringDateColumnA], TODAY(),

AND

CALCULATE(SUM(DateTable[NumberStringWORKDAY]),DATESBETWEEN(DateTable[TextStringDate],’SharePointList'[TextStringDateColumnA],’SharePointList'[TextStringDateColumnD])))

Anyone know how to pass multiple measures in the switch?

This is what I have right now.

Measure Selection =

SWITCH(TRUE(),

VALUES(‘Table1′[Measure]) = “Fees”, [YTD Fees],

VALUES(‘Table1′[Measure]) = “Rate”, [YTD Rate],

BLANK())

Currently, if I select “Fees” I get YTD Fees.

Can I modify the DAX to something which gives me YTD Fees and PYTD Fees? And if it is then can I get more than 5 measures at a time?

Hi, How can I bucket negative sales variance range using Switch dax? Eg:

=SWITCH(TRUE(),

AND([ Variance]>=10000, [Variance] >0), “Up to $10,000”,

AND([Variance] – 500), “Negative Group up to -500”?

That was super helpful. I was trying out multiple other ways but this is spot on! Thanks for posting.

Hi, I’m trying to write a switch formula where the results uses a measure, but instead of showing the result it gives me a 0 instead. For example my formula is below.

=switch(TRUE(),

[NetValidating]>[NetModel], [NetValidating],

[NetModel]>[NetValidating], [NetModel],

[NetValidating]=[NetModel], [NetModel])

Can someone help me convert this to DAX, tried many times but failing.

CASE WHEN “Segments”.”Sub Sub Segment (Code)” IN (‘SLC’, ‘SLG’, ‘SLK’)

THEN ‘PS-SLK’

WHEN “Segments”.”Sub Sub Segment (Code)” IN (‘NPWL’, ‘SEPM’, ‘SCWL’)

THEN ‘PS-WL’

WHEN “Segments”.”Sub Sub Segment (Code)” IN (‘WL10’, ‘WCH1’, ‘OTHER-SPWL’, ‘MSM’, ‘WOH1’, ‘XWS’)

THEN

CASE WHEN “- Sales Hierarchy levels”.”Level 6″ IN (‘ES’, ‘PS’)

THEN

CASE WHEN LEFT(“Jobs and Services”.”Job Type”,5)=’ES-CH’ THEN ‘ES-CH’

WHEN “Jobs and Services”.”Job Type” IN (‘ES-OH-NO: Non Operational Open Hole’, ‘ES-OH-Ops: Operations Open Hole Evaluation’, ‘TB: Thrubit’)

THEN ‘ES-OH’

WHEN “Jobs and Services”.”Job Type” IN (‘PS-WL-NO: Non Operational PS Wireline’, ‘PS-WL-Ops: Operations Production Services Wireline’, ‘EP-WLCH: EP Wireline CH — NAM only’)

THEN ‘PS-WL’

WHEN “Jobs and Services”.”Job Type” IN (‘PS-SLK-NO: Standby – No Operations’, ‘PS-SLK-Ops: Call Out Contract – Operations’, ‘PS-SLK-Rental-Ops: Rental Contract – Operations’)

THEN ‘PS-SLK’

WHEN “Jobs and Services”.”Job Type” IN (‘IS: Integrated Services’, ‘PMG: Integrated Project management’)

THEN

CASE WHEN “Segments”.”Sub Sub Segment (Code)” = ‘WCH1’

THEN ‘PS-WL’

ELSE ‘ES-OH’ END

ELSE ‘Recharges & Credits’ END

WHEN “- Sales Hierarchy levels”.”Level 6″ = ‘ES/PS’

THEN

CASE WHEN “Well Data (Invoice)”.”Type” IN (‘Development’, ‘Exploration’, ‘Old Well’)

THEN

CASE WHEN LEFT(“Jobs and Services”.”Job Type”,5)=’ES-CH’

THEN ‘ES-CH’

WHEN “Jobs and Services”.”Job Type” IN (‘ES-OH-NO: Non Operational Open Hole’, ‘ES-OH-Ops: Operations Open Hole Evaluation’, ‘TB: Thrubit’)

THEN ‘ES-OH’

WHEN “Jobs and Services”.”Job Type” IN (‘PS-WL-NO: Non Operational PS Wireline’, ‘PS-WL-Ops: Operations Production Services Wireline’, ‘EP-WLCH: EP Wireline CH — NAM only’)

THEN ‘PS-WL’

WHEN “Jobs and Services”.”Job Type” IN (‘PS-SLK-NO: Standby – No Operations’, ‘PS-SLK-Ops: Call Out Contract – Operations’, ‘PS-SLK-Rental-Ops: Rental Contract – Operations’)

THEN ‘PS-SLK’

WHEN “Jobs and Services”.”Job Type” IN (‘IS: Integrated Services’, ‘PMG: Integrated Project management’)

THEN

CASE WHEN “Segments”.”Sub Sub Segment (Code)” = ‘WCH1’

THEN ‘PS-WL’

ELSE ‘ES-OH’ END

ELSE ‘Recharges & Credits’ END

ELSE ‘Recharges & Credits’ END

ELSE ‘Recharges & Credits’ END

ELSE ‘Recharges & Credits’ END