cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stuznet Member
Member

Drill through Count Based on Two Dates

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 

Capture2.PNG

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.

 

Capture.PNG

 

Capture3.PNG

 

Here is the PBIX file

 

Any idea how do I fix this issue?

 

Really appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Stuznet Member
Member

Re: Drill through Count Based on Two Dates

@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.
Capture.PNG

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.

Capture1.PNG

2 REPLIES 2
TeigeGao New Contributor
New Contributor

Re: Drill through Count Based on Two Dates

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

Highlighted
Stuznet Member
Member

Re: Drill through Count Based on Two Dates

@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.
Capture.PNG

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.

Capture1.PNG