Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Thanks in advance!
Solved! Go to 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])))
Proud to be a 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.
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])))
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |