Forum Discussion

jennab8268's avatar
jennab8268
Frequent Visitor
2 years ago

Drill Through with Multiple Measures

I am working on a report that I would like to drill through regarding employee turnover. My measure looks like this:

 

Departing Employees = CALCULATE(DISTINCTCOUNT('TableName'[Employee Name]), filter('TableName, 'TableName'[Termination Date] >=FIRSTDATE('Date'[Date]) && 'TableName'[Termination Date]<=LASTDATE('Date'[Date])))

 

This is a measure that calculates the amount of departing employees in the company in the slicer selected date timeframe. There is no relationship between Date Table and "TableName"

 

An additional measure that I cannot drill through is:

 

Voluntary Terms =

CALCULATE(

    [Departing Employees],

    KEEPFILTERS('TableName'[Termination Reason = "Voluntary")

)

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jennab8268 ,

    Your existing measure calculates the number of departing employees within the selected date range. It’s a valuable metric to track turnover.However, since there’s no direct relationship between the Date Table and “TableName,” you might encounter limitations when trying to drill through.To improve drill-through capabilities, consider creating a bridge table that connects the Date Table and “TableName.” This bridge table can hold unique combinations of dates and employee IDs.

    Voluntary Terms

     You can try to create a calculate column:

    IsVoluntaryTermination = 'TableName'[Termination Reason] = "Voluntary"

    Then modify your “Voluntary Terms” measure:

    Voluntary Terms =
CALCULATE(
    [Departing Employees],
    'TableName'[IsVoluntaryTermination] = TRUE()
)

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.