Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
julesdude7
Frequent Visitor

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

Hi,

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

Driver NameDriver NumberIn DateOut Date
A B11111108 July 202304 August 2023
A B11111108 September 202319 October 2023
A B11111105 November 2023 
A W22222208 July 202324 August 2023
A W22222208 October 2023 
A D33333308 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?

3 REPLIES 3
lbendlin
Super User
Super User

Your measure can be simplified.

 

lbendlin_0-1707353666830.png

 

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

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?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.