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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Simkia
Frequent Visitor

If statement with condition from two non-related tables

Hello!

I would think that this would be rather simple, but I'm scratching my head a lot on this one.

I would like to make an overview of how many active orders I have on any given date, in order for an order to be active I want it to say:

date order is received <= max date

&& date order is completed > max date.

 

I have a dimCalendar table and I have another table with some order received dates (Tilgået Doc.) and orders completed dates (Udført Doc. Date) in it.

Orders table:

Simkia_0-1636896017331.png 
Date table:

     Simkia_1-1636896030462.png

 

I then figured that I wanted to make some sort of column in the DimCalendar table that counts how many orders I have received on every date, but to make this column without an active relationship between the two tables.

The reason for this is that, I figured I would put it into this formula, where it says "Datokolonne", and I would expect to be able to see how many active orders I have on any given date. 

 

 

Column 1 = 
    CALCULATE(
        SUM(Datokolonne),
        filter(
            dimCalendar,
            dimCalendar[Date] >= max('AX Data'[Tilgået Doc.]) 
            && dimCalendar[Date] < max('AX Data'[Udført Doc. Date])))

 

 

 

 

 

Any other solutions to my issue would also be greatly appreciated! 

Thanks in advance!

1 ACCEPTED SOLUTION

you add at the end as a argument of the calculate dax the function userrelantionship

Column 1 = 
    CALCULATE(
        SUM(Datokolonne),
        filter(
            dimCalendar,
            dimCalendar[Date] >= max('AX Data'[Tilgået Doc.]) 
            && dimCalendar[Date] < max('AX Data'[Udført Doc. Date])))




Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
StefanoGrimaldi
Resident Rockstar
Resident Rockstar

you can add to that dax a relatnionship function: https://docs.microsoft.com/en-us/dax/userelationship-function-dax to use a inactive relantioship between the tables that evaluates using that relantionship only for that dax formula context, add it as a filter of the calculate function. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




I'm not entirely sure how to write the column code, that is supposed to count how many orders I have received on every date. I figured it would be something similar to 

Column 2 = 
    if('AX Data'[Tilgået Doc]=dimCalendar[Date],+1,0)

However it does not allow me to do this, even if I add the USERELATIONSHIP. 

How would you make a column in my Calendar table that adds up orders received on every individual date, whilst not having an active relationship?

you add at the end as a argument of the calculate dax the function userrelantionship

Column 1 = 
    CALCULATE(
        SUM(Datokolonne),
        filter(
            dimCalendar,
            dimCalendar[Date] >= max('AX Data'[Tilgået Doc.]) 
            && dimCalendar[Date] < max('AX Data'[Udført Doc. Date])))




Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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