topic Re: Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column in DAX Commands and Tips

Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column

clp7285 — Tue, 22 Mar 2022 21:47:21 GMT

I currently have a slicer called ICM Managed Flag in my Power BI Report that is text data type using a field called ICM Managed which is whole number data type using the values of 1 and 0. I need to replace the values of 1 to "Y" for yes and 0 to "N" for No.

I am having a hard time getting the whole numbers replaced by "Y" "N" text and I also need a "Blank value." I can't seem to change the data type so they are both text. I get the message DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

I decided to use the SWITCH function with FORMAT.

VALUE function can only be used one time so I'm not sure that would work.

This is the DAX expression I created.

ICM Managed Flag = SWITCH(TRUE(),

ICM_Weekly_Data[ICM Managed]=1,FORMAT(ICM_Weekly_Data[ICM Managed],"Y"),

ICM_Weekly_Data[ICM Managed]=0,FORMAT(ICM_Weekly_Data[ICM Managed],"N"))

It's still returning numbers. Now instead of returning 1 or 0 it is returning 365 or 0 instead of Y or N. Is SWITCH the right function to use? I don't know how to fix this to return "Y" or "N" or to replace values.

Re: Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column

clp7285 — Tue, 22 Mar 2022 22:41:34 GMT

Would IF Function work better than SWITCH in this case?

Re: Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column

tamerj1 — Wed, 23 Mar 2022 03:22:57 GMT

Hi clp7285

SWITCH is actually IF Else. Are you trying to create new calculated column? If so you can simply use

CM Managed Flag = SWITCH ( TRUE (), ICM_Weekly_Data[ICM Managed] = 1, "Y", ICM_Weekly_Data[ICM Managed] = 0, "N" )

Re: Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column

clp7285 — Wed, 23 Mar 2022 16:33:40 GMT

I changed this to an IF statement

ICM Managed Flag = IF(ICM_Weekly_Data[ICM Managed]= 1, "Y", IF(ICM_Weekly_Data[ICM Managed]= 0, "N", BLANK()))

I'm getting N and Y on my Managed Flag Slicer but not Blank option. The ICM Managed field on Data tab has Select All, Blank, 0, 1. I need to have Blank display on ICM Managed Flag. What is the correct syntax to get Blank to display in dropdown slicer menu?

Re: Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column

tamerj1 — Wed, 23 Mar 2022 16:44:20 GMT

Hi clp7285
Try the strict equal "==". No need to have the 2nd argument of IF as by default it returns blank.

ICM Managed Flag = IF ( ICM_Weekly_Data[ICM Managed] == 1, "Y", IF ( ICM_Weekly_Data[ICM Managed] == 0, "N" ) )

Re: Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column

clp7285 — Wed, 23 Mar 2022 16:53:57 GMT

Hi tamerj1,

I can't have Blank = 0 as 0 is "N." Is strictly zero ==0 essential the same as =0? Blank needs to mean blank cell, not a cell that has a 0 in it. I think I would need 3 arguments total, one for =1, "Y" =0, "N", one for blank cells.

Re: Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column

tamerj1 — Wed, 23 Mar 2022 17:20:52 GMT

clp7285

For some reason DAX sometimes considers nulls as zero and the opposite is true. I thought maybe this is why your code is not retuning blank as there is a possibility that the blanks in your data are considered zero and therefore returns "N". Did you try?

Re: Need to change slicer values from 1,0 to "Y" "N" by using DAX in new column

clp7285 — Wed, 23 Mar 2022 17:48:36 GMT

Hi tamerji1,

This did fix the problem. Thanks so much for your help.