Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
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.
Thanks so much.
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.
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?
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.
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |