Skip to main content
cancel
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.

Reply
EmaVasileva
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 =

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




 

2 REPLIES 2
TomMartens
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.


Regards,
Tom



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

Helpful resources

Announcements
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

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.