Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago
Solved

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 

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!

Need Help
Show and Tell
Tutorial Requests
  • Stuznet's avatar
    Stuznet
    7 years ago

    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.

2 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    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's avatar
      Stuznet
      Helper V
      7 years ago

      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.