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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper V
Helper V

DirectQuery Dax - Dates comparison

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):
sc13.png  sc12.png

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 =


VAR _d2 =


        MAX ( 'Locations2'[Date Changes] ),

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


VAR _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):

Any help will be appreciated. Thank you.


Super User
Super User

Hey @EmaVasileva ,


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.


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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:

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

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


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.