Drill through Count Based on Two Dates

[Stuznet]

Hi guys,

I have 2 dates fields and I need to count the prjacct based on two dates. 

So, I created a Date table, then I create another table for the

End Date

End Date = SUMMARIZE(Data,
                        [End Date],
                        [PrjAcct])

Then I create relationships 

The problem I'm having is when I drill through Row 1 "33" I'm not getting the correct result in Page 2. Is giving me 110 instead.

Here is the PBIX file

Any idea how do I fix this issue?

Really appreciated!

[TeigeGao]

Hi @Stuznet ,

The measure can't be used in drill through, as a result, the filter on Row haven't transferred to the report page 2.

Best Regards,

Teige

[Stuznet · Accepted solution]

@TeigeGao 

I found a solution. I follow this link and I aggregate the dates.

I recreate the relationship and set the End Date relationship to Inactive in order to utilize the

USERELATIONSHIP function. and make the Start Date an active relationship.

I rewrite the measure

Measure = SWITCH(TRUE(),
            MAX('Row'[Row]) = "Row 1",
                COUNT(Data[PrjAcct]),
                
            MAX('Row'[Row]) = "Row 2",
                CALCULATE([Count of ProjAcct],USERELATIONSHIP('Dim Date'[Date], Data[End Date])))

Drag the dim date and the measure to the data table. Now when I drill through is showing me the correct result.