DirectQuery Dax - Dates comparison

[Anonymous]

Hi Team,

I need help with transformation of a Dax calculation which works in Import mode, but it’s not applicable to Direct query mode.

I have three tables, currently used in Import mode, but I need them to be in Direct query. They will use SQL as a source (here, the screens are from excel):
  

Currently, my “CompareDates” column compares every row of Tasks[StartTime] and Locations2[DateChanges], and  if Tasks[StartTime] is latest then Locations2[DateChanges] it returns yes, otherwise no:
CompareDates =

VAR _d1 =

   (Tasks[StartTime])

VAR _d2 =

    CALCULATE (

        MAX ( 'Locations2'[Date Changes] ),

        CROSSFILTER (Locations1[Location], 'Locations2'[Location], BOTH)

    )

VAR _comp =

    IF(_d1>_d2,"Yes","No")

RETURN

    _comp

In direct query mode the calculate/crossfilter functions don’t work, or if I try to use a measure instead of a column, I need to aggregate _d1 as Max/MIN, but this doesn’t work fine. Also, currently I’m using this calculation as a Slicer for a table-to filter data related to “Yes”/”No”.  If it’s not possible to transform the formula as a calculated column, Plan B is to be a measure, which to be used as a hidden filter for two tables-one to filter all “Yes” results, and the other  “No”.

If it helps, here is a sample PBI file with my data (but it is in Import Mode): https://drive.google.com/file/d/1xU0flEH1WhuDXvVUKwZEDmbfQVSvVceE/view?usp=sharing

Any help will be appreciated. Thank you.

[TomMartens]

Hey @Anonymous ,

my recommendation if you move to DQ, create a single table instead of using a 1:1 relationship between the tables Locations1 and Locations2.
Ask the owner of the data source if they can provide this table or a view that combines both tables into a single table.

If this does not work, please provide sample data that can be easily imported into a SQL tables.

Regards,
Tom

[Anonymous]

Hi @TomMartens ,

Currently it is not possible to combine the two tables, I will bring this to the owner. 

I uploaded the three excel files for Locations1&2,Tasks: https://drive.google.com/drive/folders/1-SRRo_3gopc_d4LF-EpU2D3Y4TBNb4GO?usp=sharing

Please let me know if anything else is needed. Thank you for looking into my case.