CASE WHEN Statement Using DAX Comparing a Text AND Number Column

Kyle_Wolfe_Ohio — Wed, 22 May 2024 13:52:25 GMT

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

Re: CASE WHEN Statement Using DAX Comparing a Text AND Number Column

some_bih — Wed, 22 May 2024 14:48:25 GMT

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.

Re: CASE WHEN Statement Using DAX Comparing a Text AND Number Column

Anonymous — Thu, 23 May 2024 06:18:42 GMT

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:

topic Re: CASE WHEN Statement Using DAX Comparing a Text AND Number Column in DAX Commands and Tips

CASE WHEN Statement Using DAX Comparing a Text AND Number Column

Re: CASE WHEN Statement Using DAX Comparing a Text AND Number Column

Re: CASE WHEN Statement Using DAX Comparing a Text AND Number Column