DAX Fridays! #15: SWITCH

Learn how to use the function SWITCH in DAX/ Powerpivot.
Switch is the equivalent of IF but it has easier syntax and therefore it is easier to read.
PREVIOUS VIDEO: • DAX Fridays! #14: SUMM...
NEXT VIDEO: -
Looking for the download file? Go to our Download Center: curbal.com/don... and then click on Dax fridays.
Keynotes:
01:45 Create an IF statement
02:37 Create a simple SWITCH calculated column
05:01 Create a SWITCH function with TRUE
08:22 Example of a wrong SWITCH function with TRUE
10:22 Create a SWITH function that returns blanks on blank rows
11:46 Create a measure with SWITCH and FORMAT
Looking for a download file? Go to our Download Center: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
Join our DAX Fridays! Series: goo.gl/FtUWUX
Power BI dashboards for beginners: goo.gl/9YzyDP
Power BI Tips & Tricks: goo.gl/H6kUbP
Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
#daxfridays #curbal #SUBSCRIBE
► Linkedin: goo.gl/3VW6Ky

[@joanneosborne2428]

Thanks, Ruth. I found this video very useful. I watch all of your training videos, and I learn so much. 😊❤

[@CurbalEN]

💛💛

[@MrRakesh448]

Hello Ruth, Switch gives better performance than IF. Specially when we write nested logic/ expressions.

[@CurbalEN]

Hi! Thanks for sharing :) /Ruth

[@jagathfernando4682]

Thanks, Ruth. Excellent presentation.

[@tantipornanan]

Hi Ruth could you please explain why u input true() expression ? It seems you can write if formulas inside of there.

[@dariom5073]

Thanks Ruth! excellent explanation. I love your voice ;)

[@CurbalEN]

You have now only 66 dax fridays videos left ;) Thanks for the feedback! /Ruth

[@NorbertoVeraReatigaNVR]

Gracias Ruth, muy buena elección de casos para el ejemplo de uso de la función!! todo muy claro, aprendí el uso de esta muy útil pero sencilla función...

[@CurbalEN]

+Norberto Vera Reatiga Gracias Norberto! /Ruth

[@tlee7028]

Thank you 🙏 but under what situations do we use Switch True or false?

[@danielballesta8348]

Hola Ruth. Muchas gracias por esta explicación. Debo admitir que entendí bien los primero ejemplos, pero con el último (formato de moneda) todavía tengo la duda de por qué fue necesario usar la función HasOneValue junto al IF.

[@ayandapeter1681]

Hi, Yes I actually found this to be very helpful...Expressing my gratitude from South Africa

[@CurbalEN]

Thanks Ayanda!! :)

[@zubairso]

This is very useful, although I would also like to learn about nested SWITCH, SWITCH on dates and SWITCH with AND/OR function.

[@nehachak]

Hi Ruth. Can you please explain the use of Switch in a measure? Has Values replaced True in a measure? Thanks in advance.

[@TomeuMatas]

Hi Ruth Thanks for your useful videos. I've been using VBA and I'm just begining with DAX, so sometimes I'm just a little lost. In a comment you say: " I prefer to do all my transformations in Power query". I understand the value of this comment but I don't know how to learn the Power Query's language. Could you help me? Do you know some playlist?

[@CurbalEN]

Hi Tomeo, Miguel Escobar (the power user) has a lot of videos and I run a Power Query series every Monday. Not aware of any other playlist.... I intent to run a small serie on power query basics, stay tune for that :) /Ruth

[@siyandamagubane8711]

Hi, I heard you can use the SWITCH function for number formats? I always get the syntax wrong every time I try :( ! Could you please give me an expression to show percentages and whole numbers in one column? Or any other best approach to address this? Thank you!

[@efficiencydna897]

this video is very useful, thank you

[@CurbalEN]

+东写西读 Fantastic! Glad it helped you! /Ruth

[@javohirfarhodov6754]

why you used Excal instead of Power BI ?

[@CurbalEN]

Don’t know , try something else ?

[@xinranji775]

So for the last example, is the values() necessary?Can it just be SWITCH(ProductData[Country],.......)?

[@rolandkc]

Hi Ruth, I am fairly new to this PowerBI stuff. I am starting to use at work. One question, is there any limit to the number of pairs you list in the SWITCH function? I have a fairly long Chart of Accounts which I need to classify/catergorise into report lines [which is comprised of a bunch of Accounts, some in a range but some not] in our Income Statement and Balance Sheet.

[@CurbalEN]

+Roland Kc Hi Roland! I have never hit the limit for switch and it is not in the documentation, so I am afraid I don't know. If you are planning to use a calculated column for that I would recommend to do it in Power Query instead. Here is an example on how to do it: social.technet.microsoft.com/Forums/en-US/c94378ec-98ac-42c6-8a92-5f247b9e3b25/if-function-in-power-query?forum=powerquery /Ruth

[@rolandkc]

Thank you, Ruth, for your prompt response. This is sincerely appreciated. What is the reason for your recommendation to do it through Power Query with IF instead of through Power Pivot with SWITCH? Is it because it is more efficient that way? I am just concerned that I would get lost in the amount of nested IFs i would require as the list of my accounts is over 2000.

[@CurbalEN]

+Roland Kc Hi Roland, I prefer to do all my transformations in Power query, so when the data hits power bi it is in the form and shape my users want it. If you use a calculated column you will get performance issues, if you use a measure you might be ok (?) Such a big transformation is perhaps better to do it at the source if your IT Dept can help you with it. I have never tried to make a 2000 if statements, 😳 so I am interested to know how Power BI performs. Please keep me updated! /Ruth

[@rolandkc]

Thank you, Ruth, for your advice which definitely makes sense. I will keep you updated...

[@CurbalEN]

+Roland Kc My pleasure Ronald :)

[@ortaltrabelsi]

Hi, Thanks for the video. I have 2 Q: 1. What is the meaning of the comma in "0,2" for example? I am not familiar with it, so if you have any video on it, please let me know. 2. I can't enter just a column name in the switch function, as the first varaible. (In the "expression" place) I am getting this error: "A single value for column 'Messure name' in table 'UserMessures' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." Thanks :)

[@sgk1966]

Hi Ruth Many thanks for the informative video. I have a doubt as to whether the following CASE statement in VBA can be done in DAX using SWITCH.……. CASE “Vendors”, “Customers”, Let [CFGroup] = “WC Changes” CASE “Depreciation”, “Amortization” Let [CFGroup] = “Non Cash” ……. ……….Trying this with the SWITCH CF Group = SWITCH ( TRUE (), 'Cash FLow Grouping Table'[Parent Group] = "Vendors" || "Customers", "WC Changes", 'Cash FLow Grouping Table'[Parent Group] = "Depreciation" || "Amortization", "Non Cash" ) This gives an error. Does this mean that SWITCH does not allow OR like this or am I making any mistake? Alternate is to have each line to check one. Was trying to see if we can avoid it. Also, if we use variable also, it gives an error. CF Group = SWITCH ( VAR v1 = "WC Changes" VAR v2 = "Non Cash" RETURN 'Cash FLow Grouping Table'[Parent Group], "Vendors" v1, "Customers",V1, ……….. Is this the normal behaviour?

[@CurbalEN]

+Gopa Kumar Hi Gopa, According to the documentation the expression used in switch has to return a single scalar value, where the expression is to be evaluated multiple times (for each row/context). If you are using an OR condition it will return a table, so you can't use it like that. /Ruth

[@sgk1966]

Thank you Ruth for the clarification.

[@CurbalEN]

+Gopa Kumar My pleasure 😊 /Ruth

[@mokafc]

Amazing, thank you

[@CurbalEN]

You welcome, glad it helped you :) /Ruth

[@lorinamuca8239]

Hello Ruth. I am trying to use the "Switch" function in a different way. I'm doing an Economic Analysis and showing values for "budget" and "forecast" in a Matrix visual. I created this measure with "switch function" in which for every category i filter i see all values of that category filtered. Actually, i have just two categories and this filter works just for one of categories i filter. I don't understand why it's not working for the other one. Do you have an idea? Have you ever made something like this? Thank you.

[@CurbalEN]

Hi Lorena, to get specific help, could you please post in the power bi community? Make sure you post some sample data too! /Ruth

[@lorinamuca8239]

Hello Ruth. I solved it!:-) Your video: "Change measures using slicers in Power BI Desktop- Power BI Tips & Tricks #20" was really usefull. Thank you.:-)

[@CurbalEN]

Fantastic! Glad it helped :) /Ruth

[@faustogalvan2342]

Muy buen aporte y súper entendible, muchas felicidades. 🏆🏆🏆

[@CurbalEN]

Excelente! Me alegro :) /Ruth

[@areekorn1]

Hi Ruth , at time line 6.33 the SwithDic Column is change data to number format , I am having difficulty changing it to proper 0.5 data type decimal number if i change i keep getting 5 and 6 and 2 instead of 0.5,0.6 and 0.2 thanks you

[@vijay_]

Use calculations option in transform tab and divide that column by 100

[@tchaitow]

Great video. is it possible to do 2 things if a condition is met? eg if country is France, return measure "Euros" And filter table Country by "France" ? Thanks

[@CurbalEN]

Maybe, but the devil is always in the details! Post your scenario with specifics in the power bi community to get detailed help.

[@danielbugim577]

Gracias Ruth! Can I send a doubt for e-mail for you? I think it is simple, but I have to sendo images to explain.

[@CurbalEN]

Hi Daniel, Can you post it in the power bi community? That is the best place :) /Ruth

[@denysss5872]

And what can we do with zero prices in the result table?

[@claudineitaiacolo5131]

Hi Ruth, how are you? Could you help me about this error? I can not identify where the measure problem is. Thanks and congratulations for the channel. It´s very good!!! :-) .Formato Preço = IF(HASONEVALUE(ProductData[Country]); SWITCH(VALUES(ProductData[Country]); "Europé";FORMAT(ProductData[Tot Revenue];"₴ 0,00"); "USA";FORMAT(ProductData[Tot Revenue];"USD 0,00"); "India";FORMAT(ProductData[Tot Revenue];"€ 0,00"); "China";FORMAT(ProductData[Tot Revenue];"¥ 0,00"); FORMAT(ProductData[Tot Revenue];"0"))) message error: It was not possible to determine a unique value for the 'Tot Revenue' column in the 'ProductData' table. This can happen when a measurement formula refers to a column that contains many values, without specifying an aggregation such as min, max, cont, or sound to get a single result.

[@CurbalEN]

+Claudinei Taiacolo Hi Claudinei! Instead of referring to ProductData[Tot Revenue], you need to refer to [Tot Revenue] where [Tot Revenue]=SUM(ProductData[Tot Revenue]. Hope this helps and thanks for the feedback! /Ruth

[@claudineitaiacolo5131]

Oops !!! of course, what a distraction ... tks!!! Sorry!!!!

[@CurbalEN]

+Claudinei Taiacolo It happened to all the time in the beginning!! /Ruth

[@michaelkarlsson1461]

Hi Iam trying to do a switch to a calculated column that from a column that is time formated and looks like 14:33, 22:21, 01:23 etc. Problem is Iam getting C in all rows with this; NewColumn = SWITCH(TRUE(); 'data'[Time]=1730;"A"; 'data'[Time]=0659 && 'data'[Time]=2159;"B"; "C")

[@CurbalEN]

+Michael Karlsson Difficult to say without seeing your data. Best solution is to post a question in the power bi community with some sample data. /Ruth

[@michaelkarlsson1461]

Curbal did email you.

[@krishnamanjunatha6183]

please, can u brief how did u inserted country currency symbol in format function

[@CurbalEN]

+Krishna Manjunatha Hi Krishna, Here is a video about the format function: m.kzbin.info/www/bejne/d5bSkplngKirb5I /Ruth

[@krishnamanjunatha6183]

i am asking for Euro currency 0.00, INR currency 0.00 in format function

[@CurbalEN]

+Krishna Manjunatha You mean like in the minute 12:00 of the video? /Ruth

[@krishnamanjunatha6183]

yeah u right. Format(measurename,"currencyname0.00"). i want to know how did you inserted currency symbol for Euro, India with value :)

[@CurbalEN]

You just need the shortcut. Follow this steps: www.quora.com/How-do-I-type-Indian-Rupee-symbol-from-keyboard-in-Windows-10 /Ruth

[@tonyxd8]

How do i use summarize, row, rank and topN on power Pivot? i dont know what im doing wrong :/ Like your videos! :)

[@CurbalEN]

+Helbert Cabana Have you seen y videos on those functions? /Ruth

[@tonyxd8]

Curbal yes I did but I couldn't use them on power pivot. :/ It's a homework. I have searched a lot even in English ,i'm a Spanish speaker, and couldn't find info on power pivot , only on power BI. :(

[@tonyxd8]

I'd be really thanked with you if you use row function for the next dax Friday. thanks for replying :D

[@CurbalEN]

+Helbert Cabana There is no difference, why can't you use them? /Ruth

[@tonyxd8]

Curbal really? so, I don't know what I'm doing wrong. I'll have to try them again I guess

[@piesogrodnika572]

Hi Ruth, in kzbin.info/www/bejne/Y6rOnJR3qdF2g8U what if for the same [ProductCategory] (ie. "City Bikes") you will have values for different countries/currencies (ie. for "Europe" in "EUR" and "Unites States" in "USD"). Is the "Grand total" going to sum up values for different currencies (which obviously is wrong)?

[@vinit9426]

Not able to download the excel

[@CurbalEN]

Looking for the download file? Go to our Download Center: curbal.com/donwload-center and then click on Dax fridays. /Ruth

[@renjilpaul]

HI Need a help base on DAX Upload a table "Customer Names" to datamodel, which includes 2 columns only, "Names" and "female Names". "Names" column includes male and female names of all customers. " Female names"colunm includes female names only want to create a new column "Gender" with value " F" ,if the "female Names" is present anywhere in the "Customer Names" column .Other wise show " M". For example Table : Customer Names "Names" "Female names" "Gender" Alexa Alexa F John M mariya Mariya F alexa F ALEXA F alexa sam F zzzalexppp F Advanced Thanks......