Forum Discussion
SUMMARIZE with dynamic filter from another table
Community ChampionThank 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.
This dashboard is aiming at end user to pick "Work"/"Travel"/"Break" from ticking/unticking boxes from the filter, not amending the DAX formula...
Is there a way of not the red part below is "whatever is selected from the filter"?
CALCULATE(SUM(Database[HOURS]), Database[TYPE] IN {"Travel", "Work", "Break"})- 123abc
To create a dynamic filter that allows end-users to select "Work", "Travel", or "Break" from ticking/unticking boxes without directly amending the DAX formula, you can utilize a disconnected table along with measures and slicers. Here's how you can achieve this:
Create a Disconnected Table: Create a new table in Power BI with three rows corresponding to "Work", "Travel", and "Break". This table will serve as your filter table.
Create Relationships: Establish a relationship between the filter table and the main data table based on the "Type" column. This will allow you to filter the main data table based on the selections made in the filter table.
Create a Measure: Create a measure that calculates the total hours based on the selections made in the filter table. Use the SELECTEDVALUE function to determine which options are selected in the filter table.
TotalHoursFiltered =
CALCULATE(
SUM(Database[HOURS]),
FILTER(
Database,
Database[TYPE] IN {
SELECTEDVALUE(FilterTable[Type1]),
SELECTEDVALUE(FilterTable[Type2]),
SELECTEDVALUE(FilterTable[Type3])
}
)
)In this measure, FilterTable[Type1], FilterTable[Type2], and FilterTable[Type3] correspond to the three types in your filter table.
Create Slicers: Create slicers based on the values in your filter table. Users can then select or deselect the options in the slicers to dynamically filter the data.
By following these steps, you can create a dynamic filter that allows end-users to select "Work", "Travel", or "Break" from ticking/unticking boxes without directly amending the DAX formula. The measure dynamically adjusts based on the selections made by users in the slicers.
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.