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.
- 123abc
Thank you for the clarification. It seems like you want your summarized table to dynamically filter hours based on the types you include or exclude. To achieve this dynamic filtering in Power BI, you can follow these steps:
Use a DAX Measure for Dynamic Filtering: Instead of relying solely on the SUMMARIZE table, you can create a DAX measure that dynamically filters the hours based on the types you include or exclude.
Here's an example DAX measure that calculates the total hours based on included types:
TotalHoursIncludedTypes =
CALCULATE(
SUM('EmployeeHours'[Hours]),
'EmployeeHours'[Type] IN {"Travel", "Work"}
)In this measure, we are using the CALCULATE function along with the IN operator to filter the hours for the types "Travel" and "Work". You can adjust the types as per your requirement.
Utilize the Measure in Visuals: You can now use the "TotalHoursIncludedTypes" measure in your visualizations such as tables or cards. This measure will dynamically calculate the total hours based on the included types.
Dynamic Filtering with Slicers: You can use slicers to dynamically include or exclude types from the calculation. For example, you can have a slicer that allows users to select the types they want to include or exclude from the calculation.
Update SUMMARIZE Table: You can still utilize the SUMMARIZE table to get the summarized view of total hours per day per employee. However, you may need to adjust your calculations to use the dynamic measure we created above for accurate filtering based on included types.
By following these steps, you should be able to dynamically filter hours based on the types you include or exclude and have your summarized table update accordingly in Power BI. Adjust the DAX expressions and measures according to your specific requirements and data model. If you encounter any issues or need further assistance, feel free to ask!
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.