Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team
Hoping to get some help with the perceived dead-end.
This is about reporting on project management. (Dates are dd/mm/yyyy)
I've a fact table that looks like
Department | Team | Activity | Activity Start Date | Activity End Date | Daily Activity Min | |||||
Dept1 | Team1 | Activity1 | 23/06/2021 | 26/06/2021 | 56 | |||||
Dept1 | Team1 | Activity2 | 27/06/2021 | 15/07/2021 | 106 | |||||
Dept1 | Team2 | Activity1 | 16/07/2021 | 28/09/2021 | 245 | |||||
Dept1 | Team2 | Activity2 | 29/09/2021 | 14/12/2021 | 150 |
I have a Calendar Dim. At this stage not connected to any of the fact Dates.
I want the output in a Matrix Visual to show like
Jun-2021 | Jul-2021 | Aug-2021 | Sep-2021 | Oct-2021 | ||||||
Team1 | 162(56+106) | 106 | ||||||||
Team2 | 245 | 245 | 395(245+150) | 150 |
The best working measure I could do is come up with number of activities between the dates. I've tried this measure
I tried changing it to sum up the Minutes - but did not work.
SUMX('Gantt Content',
CALCULATE(Sum('Gantt Content'[Daily Activity Min]),KEEPFILTERS(DATESBETWEEN('CALENDAR'[CALENDAR_DATE],'Gantt Content'[Activity Start Date],'Gantt Content'[Activity End Date]))))
Please let me know if I have missed anything to explain.
Any help is much appreciated.
Thanks
Solved! Go to Solution.
Hi @Anonymous
You have not stated the full algorithm that would cover all the cases possible in your fact table. I don't think that the above sample is representative. If it is and no other cases are possible, you should make it explicit.
However, from what you've given it follows that the simplest solution to this, at least as far as I'm concerned, would be to create a new (hidden) table that would store the row id of the fact table and the expansion (into individual dates) of the period of time between the dates. Such a table would be hidden because it'd be there just for the sake of making calculations easy and fast. The date column in the table would be connected to the Dates (your calendar) with many-to-one and one-way filtering. Then it's dead easy to create the DAX measure. It would be:
[Total Daily Activity (min)] =
CALCULATE(
SUM( FT[Daily Activity Min] ),
CROSSFILTER(
FT[RowID],
HiddenTable[RowID],
BOTH
)
)
The relationship from the FT to the hidden table would be one-to-many with one-way filtering.
But this can be even further simplified if you turn on two-way filtering on the relationship between the FT and the hidden table. Then the measure will look like:
[Total Daily Activity (min)] = SUM( FT[Daily Activity Min] )
Is this cool or what? Can it get any better than that? (and faster?) You know the answer 🙂
Of course, it's also easy to write a measure without CROSSFILTER and without the two-way filtering:
[Total Daily Activity (min)] =
SUMX(
SUMMARIZE(
HiddenTable,
FT[RowID],
FT[Daily Activity Min]
),
FT[Daily Activity Min]
)
// or even
[Total Daily Activity (min)] =
CALCULATE(
SUM( FT[Daily Activity Min] ),
SUMMARIZE(
HiddenTable,
FT[RowID]
)
)
There are many other formulations...
Can you see how simple (and fast!) the measures could be? A good model is the key to success in PBI. Not DAX. Always.
Proof that it works:
Hi @Anonymous
You have not stated the full algorithm that would cover all the cases possible in your fact table. I don't think that the above sample is representative. If it is and no other cases are possible, you should make it explicit.
However, from what you've given it follows that the simplest solution to this, at least as far as I'm concerned, would be to create a new (hidden) table that would store the row id of the fact table and the expansion (into individual dates) of the period of time between the dates. Such a table would be hidden because it'd be there just for the sake of making calculations easy and fast. The date column in the table would be connected to the Dates (your calendar) with many-to-one and one-way filtering. Then it's dead easy to create the DAX measure. It would be:
[Total Daily Activity (min)] =
CALCULATE(
SUM( FT[Daily Activity Min] ),
CROSSFILTER(
FT[RowID],
HiddenTable[RowID],
BOTH
)
)
The relationship from the FT to the hidden table would be one-to-many with one-way filtering.
But this can be even further simplified if you turn on two-way filtering on the relationship between the FT and the hidden table. Then the measure will look like:
[Total Daily Activity (min)] = SUM( FT[Daily Activity Min] )
Is this cool or what? Can it get any better than that? (and faster?) You know the answer 🙂
Of course, it's also easy to write a measure without CROSSFILTER and without the two-way filtering:
[Total Daily Activity (min)] =
SUMX(
SUMMARIZE(
HiddenTable,
FT[RowID],
FT[Daily Activity Min]
),
FT[Daily Activity Min]
)
// or even
[Total Daily Activity (min)] =
CALCULATE(
SUM( FT[Daily Activity Min] ),
SUMMARIZE(
HiddenTable,
FT[RowID]
)
)
There are many other formulations...
Can you see how simple (and fast!) the measures could be? A good model is the key to success in PBI. Not DAX. Always.
Proof that it works:
@daxer-almighty - you are almighty (in power bi at least). That was fantastic tip.
It has worked. I'm now getting exactly what was need based on your recommendation. Brilliant!!!
You said it right. It is the model that wins it for us, and not manipulation in DAX.
Hats off, legend!!!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |