CODEWITHSUNDEEP
powerbidax
haoran zhang
haoran zhang

Reputation: 51

Date comparison in power bi dax

I had a problem with my code. It returns the below error: DAX comparison operates do not support comparing values of type date with values of type text.

Basically, I want to count rows based on some conditions. And I know there is a need to convert the data type, but I am not sure how to do it.

Total Open Issues = 
--------------------
--basic info
VAR SELECTEDDATE =
    DATEVALUE(SELECTEDVALUE(Calender[FullDateAlternateKey].[Date]))
--------------------
--FIND the relvent data
VAR rlvttable =
    calculatetable(
    Tracker,
    Tracker[Catagory]="ISSUE",
    DATEVALUE(Tracker[ClosedDate])>SELECTEDDATE
       ||Tracker[ClosedDate]=""
    )
--------------------
--Results
Return
    countrows(rlvttable)

Anyone could advise me how to correct it? Thanks~

Upvotes: 1

Views: 11272

Answers (2)

haoran zhang
haoran zhang

Reputation: 51

I am trying to compare the closedDate with "". I should use blank() instead.

Upvotes: 0

Olly
Olly

Reputation: 7891

Check the data type of columns Tracker[ClosedDate] and Calender[FullDateAlternateKey] - one of them is Text, rather than Date.

To fix, you could:

  • choose a different field which is already a Date format
  • change the format of the offending column
  • use DATEVALUE in your measure, to convert the text date to a real date.

It also looks like you need to edit this statement, as these conditions conflict:

Tracker[ClosedDate]>SELECTEDDATE
   &&Tracker[ClosedDate]=""

Upvotes: 1

Related Questions