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, ...
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.

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.