Forum Discussion
SUMMARIZE with dynamic filter from another table
Hi PowerBI Community!
I have a table detailing employees hours in a day (work, travel, day). I created a SUMMARIZE table to calculate the total number of hours per day per employee but I would like it to recalculate everytime I use the Type column: only break and travel for instance.
I tried different FILTER(ALL scenarios and GROUPBY. Nothing works...
Many Thanks
11 Replies
- 123abc
Community Champion
Define 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.
- talespin
Solution Sage
Hi delphinecarnet ,
Its a Calculate table, you can't recalculate based on slicer selection.
Why don't you simply use "Database" table, it has eveything you need.
Set Column Summarization for "Hours" to Sum.
Drop Employee and Summarize tables.
Because, ultimately I'd like to filter the number of days employees go over a certain number of hours per day: I would like the possoibility to exclude "Break" (the real data split the time in many more types)
- talespin
hi delphinecarnet ,
You can do all this using original table, no measure/calculate table/column required.
Also Calculate Tables/Calculated columns are updated during refresh time, they are not influenced by slicers.
