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

[clp7285]

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.

[clp7285]

Would IF Function work better than SWITCH in this case?

[tamerj1]

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"
)

[clp7285]

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?

[tamerj1]

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" )
)

[clp7285]

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.

[tamerj1 · Accepted solution]

@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?

[clp7285]

Hi tamerji1,

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