Forum Discussion
SUMMARIZE with dynamic filter from another table
Community ChampionDefine a New Measure: Create a new measure that calculates the total hours based on the selected activity type.
Total Hours =
CALCULATE(
SUM('Employee Hours'[Hours]),
ALLEXCEPT('Employee Hours', 'Employee Hours'[Employee], 'Employee Hours'[Date]),
'Employee Hours'[Type] = SELECTEDVALUE('Activity Type'[Type])
)
Create a Slicer for Activity Type: You'll need to create a slicer using the 'Type' column from your 'Activity Type' table. This slicer will allow users to select the activity type they want to filter by.
Visualize Total Hours: Now, you can use the 'Total Hours' measure in your visualization alongside the slicer. When users select a specific activity type from the slicer, the measure will recalculate dynamically based on the selection.
This DAX measure uses CALCULATE to filter the sum of hours based on the selected activity type from the slicer. ALLEXCEPT ensures that the filtering is applied only to the 'Employee' and 'Date' columns while keeping the 'Type' column filterable.
Make sure to replace 'Employee Hours' and 'Activity Type' with the actual names of your tables and columns. This approach should dynamically recalculate the total hours based on the selected activity type without requiring additional complex filtering functions like FILTER, ALL, or GROUPBY.
This cannot be a measure as I need those "Hours per day" in a slicer: I want to know how many days employees go over a certain amount of hours per day.
I created that SUMMARIZE table to get the day/employee/total hours so I can count the occurences.
Any ideas?
- 123abc
Thank you for providing additional context. If you want to filter the number of days employees go over a certain amount of hours per day using a slicer for "Hours per day", you can achieve this by creating a measure that dynamically calculates the total hours per day for each employee, and then using this measure to count the number of days that exceed the specified threshold.
Here's how you can approach this:
- Calculate Total Hours per Day per Employee: First, create a measure that calculates the total hours worked per day per employee. You can use the SUMMARIZE function to summarize the data by employee and date, and then calculate the total hours for each day.
Total Hours per Day per Employee =
SUMMARIZE (
'YourTable',
'YourTable'[EmployeeID],
'YourTable'[Date],
"Total Hours", SUM('YourTable'[Work Hours])
)Replace 'YourTable', [EmployeeID], [Date], and [Work Hours] with your actual table and column names.
- Count Days Exceeding Threshold: Next, create a measure that counts the number of days where the total hours per day per employee exceed the specified threshold. This measure should dynamically calculate the threshold based on the slicer selection.
Days Exceeding Threshold =
VAR ThresholdHours = SELECTEDVALUE('YourSlicer'[Hours Per Day])
RETURN
CALCULATE (
COUNTROWS('Total Hours per Day per Employee'),
FILTER (
'Total Hours per Day per Employee',
[Total Hours] > ThresholdHours
)
)Replace 'YourSlicer'[Hours Per Day] with the name of your slicer and column that contains the threshold hours.
- Visualize the Data: Finally, use this measure in your Power BI report along with the slicer for "Hours per day" to dynamically filter and visualize the number of days employees go over the specified amount of hours per day.
By using this approach, you can dynamically adjust the threshold using the slicer while calculating the number of days that exceed the selected threshold for each employee..
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
The issue is the filtering to get a number of hours per day that is dynamic.
In the screencapture below, I can see that the total of hours that are "Travel" + " Work" only is 89 hours. Yet, in my SUMMARIZE table, it is still 140 hours which is "Break" added.
I would like the SUMMURIZE table to filter hours the type I include/exclude and the between to update accordingly.
Thanks in advance for all your help so far and any ideas you can think of.
