DAX Error - do not support comparing values of type Date with type Text

julesdude7 — Wed, 07 Feb 2024 17:23:51 GMT

Hi,

I have the following table called 'In Out Dates' that looks something like this:

Driver Name	Driver Number	In Date	Out Date
A B	111111	08 July 2023	04 August 2023
A B	111111	08 September 2023	19 October 2023
A B	111111	05 November 2023
A W	222222	08 July 2023	24 August 2023
A W	222222	08 October 2023
A D	333333	08 July 2023

I have a DAX measure that is the following:

In Out Status = VAR TodayDate = [Today's Date] VAR SelectedDriver = SELECTEDVALUE('Drivers List'[Driver Number]) VAR InDate = CALCULATE(MAX('In Out Dates'[In Date]), 'In Out Dates'[Driver Number]) = SelectedDriver VAR OutDate = CALCULATE(MAX('In Out Dates'[Out Date]), 'In Out Dates'[Driver Number]) = SelectedDriver RETURN IF( AND( TodayDate >= InDate, (OutDate = BLANK() || TodayDate < OutDate) ), "In", "Out" )

The measure must reference in In Out Dates table, take the driver number the user has selected in a slicer (SelectedDriver) and use this to get the latest date from the In Date column and the latest date from the Out Date column. If today's date is greater than or equal to the In Date and either the Out Date is null or today's date is less that the Out Date then the label In should be used, otherwise Out should be used.
The Today's Date measure is simply -
Today's Date = TODAY()
Running this however, I get an error in the visual and clicking on it it tells me 'Can't load the data for this visual - DAX comparison operations do not support comparing values of type Date with values of type Text'.

This is odd as the data types in the tables are set to date for In Date and Out Date columns and the Driver Number is text data type.

What am I doing wrong?

Re: DAX Error - do not support comparing values of type Date with type Text

lbendlin — Thu, 08 Feb 2024 00:54:32 GMT

Your measure can be simplified.

Re: DAX Error - do not support comparing values of type Date with type Text

julesdude7 — Mon, 12 Feb 2024 11:03:25 GMT

lbendlin It doesn't quite give me what I want.
Based on the data in my orginal table above, if the current date is 24/08/2023, driver 222222 should have status as "Out" because the Out Date is 24/08/2023 and this would be the case until the next date - In Date of 08/10/2023.

Re: DAX Error - do not support comparing values of type Date with type Text

lbendlin — Mon, 12 Feb 2024 22:11:25 GMT

if the current date is 24/08/2023

yeah, but that ship has sailed. Feels like your requirement is slightly different from what you describe?

topic DAX Error - do not support comparing values of type Date with type Text in DAX Commands and Tips

DAX Error - do not support comparing values of type Date with type Text

Re: DAX Error - do not support comparing values of type Date with type Text

Re: DAX Error - do not support comparing values of type Date with type Text

Re: DAX Error - do not support comparing values of type Date with type Text