CASE WHEN Statement Using DAX Comparing a Text AND Number Column

[Kyle_Wolfe_Ohio]

Good morning:

I am trying to create a visual that will display the probationary period length for all new starts at my organization.  As you can imagine with multiple labor unions, promotions, demotions, lateral transfers, new hires to the agency, etc. there are quite a few sceanrios. 

Furthermore, my comparisons use the future and previous bargaining unit text column and future and previous pay range number.  I am thinking SWITCH would work, but getting an error saying, "DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."  Also, I am not sure if the NULL for new hires is done correctly (I just need new hires to be 365 days)

Here is what I am trying to do (NOTE I WILL HAVE A FEW MORE ADDED AT THE END, but this will suffice):

SWITCH STMT =

SWITCH(TRUE(),

    'New Hire Orientation Dash Dataset'[Previous Pay Range] is null, "365",

    'New Hire Orientation Dash Dataset'[Future Labor Union] = "A" && 'New Hire Orientation Dash Dataset'[Future Pay Range] > 'New Hire Orientation Dash Dataset'[Previous Pay Range], "180",

    'New Hire Orientation Dash Dataset'[Future Labor Union] = "B" && 'New Hire Orientation Dash Dataset'[Future Pay Range] > 'New Hire Orientation Dash Dataset'[Previous Pay Range], "180",

    'New Hire Orientation Dash Dataset'[Future Labor Union] = "C" && 'New Hire Orientation Dash Dataset'[Future Pay Range] > 'New Hire Orientation Dash Dataset'[Previous Pay Range] &&

                'New Hire Orientation Dash Dataset' [Future Pay Range] = 1-7, "120",

    'New Hire Orientation Dash Dataset'[Future Labor Union] = "C" && 'New Hire Orientation Dash Dataset'[Future Pay Range] > 'New Hire Orientation Dash Dataset'[Previous Pay Range] &&

                'New Hire Orientation Dash Dataset' [Future Pay Range] = 23-28, "120",

    'New Hire Orientation Dash Dataset'[Future Labor Union] = "C" && 'New Hire Orientation Dash Dataset'[Future Pay Range] > 'New Hire Orientation Dash Dataset'[Previous Pay Range] &&

                'New Hire Orientation Dash Dataset' [Future Pay Range] = 8-12, "180",

    'New Hire Orientation Dash Dataset'[Future Labor Union] = "C" && 'New Hire Orientation Dash Dataset'[Future Pay Range] > 'New Hire Orientation Dash Dataset'[Previous Pay Range] &&

                'New Hire Orientation Dash Dataset' [Future Pay Range] = 29-36, "180",

    "Look Up in Application")

[some_bih · Accepted solution]

Hi @Kyle_Wolfe_Ohio DAX function SWITCH has some remarks, one of them is 

• All result expressions and the else expression must be of the same data type

 as seems your expression part is not single column (rather multiple columns: 'New Hire Orientation Dash Dataset'[Previous Pay Range], 'New Hire Orientation Dash Dataset'[Future Labor Union]...)

So basically, you should rework your calculation logic for this case, try with multiple IF as your input for SWITCH have different data type.

Link for documentation.

[v-zhouwen-msft · Accepted solution]

Hi @some_bih ,thanks for the quick reply, I'll add further.

Hi @Kyle_Wolfe_Ohio ,

Something like this?

The data type of the field 'pay range' should be Text.

Try modifying your expression:

[some_bih · Accepted solution]

Hi @Kyle_Wolfe_Ohio DAX function SWITCH has some remarks, one of them is 

• All result expressions and the else expression must be of the same data type

 as seems your expression part is not single column (rather multiple columns: 'New Hire Orientation Dash Dataset'[Previous Pay Range], 'New Hire Orientation Dash Dataset'[Future Labor Union]...)

So basically, you should rework your calculation logic for this case, try with multiple IF as your input for SWITCH have different data type.

Link for documentation.