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
Anonymous
Not applicable

Activity spanning across months

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

 

COUNTX('Gantt Content',
CALCULATE(COUNT('CALENDAR'[CALENDAR_DATE]),KEEPFILTERS(DATESBETWEEN('CALENDAR'[CALENDAR_DATE],'Gantt Content'[Activity Start Date],'Gantt Content'[Activity End Date]))))

If there is a better way to achieve above, I am all ears. It takes about 30+secs for the Matrix visual to load with 1460 rows of calendar dates and 200 rows of fact data. GRRR!!!
 

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

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

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:

daxeralmighty_0-1629555894008.png

daxeralmighty_1-1629555948506.png

daxeralmighty_2-1629555994436.png

 

View solution in original post

2 REPLIES 2
daxer-almighty
Solution Sage
Solution Sage

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:

daxeralmighty_0-1629555894008.png

daxeralmighty_1-1629555948506.png

daxeralmighty_2-1629555994436.png

 

Anonymous
Not applicable

@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!!!

 

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.

Top Solution Authors