Measure to Count Rows Except ignoring slicer value from another table

[bcobrien1977]

Hi,

I am trying to calculate terminated employees by month, by division and by many other characterisitics. 

Division and Reporting Month are both slicers that are affecting the main visual page.  

On a separte page, I need to  be able to use the slicer value for division but ignore the Report Month (Selected via Slicer).

I can cacluate the amount of Hired Employees, Active Employees but the terminated employees is only pulling values for the month that the user selects instead of all employees who were terminated in that month (regardless of hire date)

How do I manipulate the data to let the division slicer impact the data but ignore the reporting month.  Both filter tables are linked to the main data.

Thanks

[littlemojopuppy · Accepted solution]

@bcobrien1977 you're not providing a lot of details about your data model so this is hard to answer.

Based on your statement "terminated employees is only pulling values for the month that the user selects instead of all employees who were terminated in that month" I'd be willing to bet that you need multiple relationships between your date table and your employees table.

I did something similar recently.  Employees have a hire date, termination date, and potentially a rehire date.  There needs to be a relationship for each of these dates and the date table.  In the measures for hired employees, use the USERELATIONSHIP function specifying the date table field and date hired as part of the CALCULATE statement to make sure you are counting by people hired in that time period.  Similarly, for terminated employees use the function specifying date table value and date terminated to make sure you count those termed in that period.

Hope this helps!

[bcobrien1977]

Thanks so much.  

[v-henryk-mstf]

Hi @bcobrien1977 ,

According to your description, you can filter external contexts by functions such as all, alexcept, etc. Refer to the following example.
Amount_formula retains all external filtering contexts (affected by slicer).
The amount_filter_month formula clears the month filtering context in the slicer.

Amount_ = sum(FactInternetSales[SalesAmount])

Amount_filter_month =
CALCULATE (
    SUM ( FactInternetSales[SalesAmount] ),
    ALL ( DimDate[MonthNumberOfYear] )
)

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

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

[bcobrien1977]

Thanks that is helpful.  It is just a strange layout where they want trending and current month and trying to balance the needs of that is tricky.

If you had more slicers to ignore, that you could extrapolate this concept?

[v-henryk-mstf · Accepted solution]

Hi @littlemojopuppy ,

Yes, the principle of screening is the same.

Amount_filter_month =
CALCULATE (
    SUM ( FactInternetSales[SalesAmount] ),
    ALL ( DimDate[MonthNumberOfYear] ),
    ALL ( [column you want to neglect] ),
    ALL ( [column you want to neglect] )
)

In addition, if you want to have more understanding, you can refer to the following related blogs.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[littlemojopuppy · Accepted solution]

@bcobrien1977 you're not providing a lot of details about your data model so this is hard to answer.

Based on your statement "terminated employees is only pulling values for the month that the user selects instead of all employees who were terminated in that month" I'd be willing to bet that you need multiple relationships between your date table and your employees table.

I did something similar recently.  Employees have a hire date, termination date, and potentially a rehire date.  There needs to be a relationship for each of these dates and the date table.  In the measures for hired employees, use the USERELATIONSHIP function specifying the date table field and date hired as part of the CALCULATE statement to make sure you are counting by people hired in that time period.  Similarly, for terminated employees use the function specifying date table value and date terminated to make sure you count those termed in that period.

Hope this helps!