CASE WHEN Statement Using DAX Comparing a Text AND Number Column

Question

Good morning:&nbsp;I am trying to create a visual that will display the probationary period length for all new starts at my organization.&nbsp; As you can imagine with multiple labor unions, promotions, demotions, lateral transfers, new hires to the agency, etc. there are quite a few sceanrios.&nbsp; &nbsp;Furthermore, my comparisons use the future and previous bargaining unit text column and future and previous pay range number.&nbsp; 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."&nbsp; Also, I am not sure if the NULL for new hires is done correctly (I just need new hires to be 365 days)&nbsp;Here is what I am trying to do (NOTE I WILL HAVE A FEW MORE ADDED AT THE END, but this will suffice):&nbsp;&nbsp;SWITCH STMT = SWITCH(TRUE(),&nbsp; &nbsp; 'New Hire Orientation Dash Dataset'[Previous Pay Range] is null, "365",&nbsp; &nbsp; 'New Hire Orientation Dash Dataset'[Future Labor Union] = "A" &amp;&amp; 'New Hire Orientation Dash Dataset'[Future Pay Range] &gt; 'New Hire Orientation Dash Dataset'[Previous Pay Range], "180",&nbsp; &nbsp; 'New Hire Orientation Dash Dataset'[Future Labor Union] = "B" &amp;&amp; 'New Hire Orientation Dash Dataset'[Future Pay Range] &gt; 'New Hire Orientation Dash Dataset'[Previous Pay Range], "180",&nbsp; &nbsp; 'New Hire Orientation Dash Dataset'[Future Labor Union] = "C" &amp;&amp; 'New Hire Orientation Dash Dataset'[Future Pay Range] &gt; 'New Hire Orientation Dash Dataset'[Previous Pay Range] &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'New Hire Orientation Dash Dataset' [Future Pay Range] = 1-7, "120",&nbsp; &nbsp; 'New Hire Orientation Dash Dataset'[Future Labor Union] = "C" &amp;&amp; 'New Hire Orientation Dash Dataset'[Future Pay Range] &gt; 'New Hire Orientation Dash Dataset'[Previous Pay Range] &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'New Hire Orientation Dash Dataset' [Future Pay Range] = 23-28, "120",&nbsp; &nbsp; 'New Hire Orientation Dash Dataset'[Future Labor Union] = "C" &amp;&amp; 'New Hire Orientation Dash Dataset'[Future Pay Range] &gt; 'New Hire Orientation Dash Dataset'[Previous Pay Range] &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'New Hire Orientation Dash Dataset' [Future Pay Range] = 8-12, "180",&nbsp; &nbsp; 'New Hire Orientation Dash Dataset'[Future Labor Union] = "C" &amp;&amp; 'New Hire Orientation Dash Dataset'[Future Pay Range] &gt; 'New Hire Orientation Dash Dataset'[Previous Pay Range] &amp;&amp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'New Hire Orientation Dash Dataset' [Future Pay Range] = 29-36, "180",&nbsp; &nbsp; "Look Up in Application")

anonymous · Accepted Answer

Hi&nbsp;some_bih&nbsp;,thanks for the quick reply, I'll add further.
Hi&nbsp;Kyle_Wolfe_Ohio&nbsp;,
Something like this?

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

Try modifying your expression:

&nbsp;

Forum Discussion

CASE WHEN Statement Using DAX Comparing a Text AND Number Column

2 Replies

Related content

IF statement comparing number and text

Compare passenger numbers

OR Statement ?

SQL Statement opcion error

Table Sort , sort order , comparer