cancel
Showing results for
Did you mean:
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:

Date table:

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!

1 ACCEPTED SOLUTION
Super User

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!

3 REPLIES 3
Super User

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!

Frequent Visitor

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?

Super User

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!

Announcements