Drill Through with Multiple Measures

[jennab8268]

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")

)

[v-xiandat-msft]

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.