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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors