SUMMARIZE with dynamic filter from another table

[delphinecarnet]

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

[talespin]

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.

[delphinecarnet]

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.

[123abc]

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])
)

1. 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.

2. 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.

[delphinecarnet]

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:

1. 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.

1. 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.

1. 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.

[delphinecarnet]

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:

1. 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"}
)

1. 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.

2. 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.

3. 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.

4. 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.

[delphinecarnet]

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:

1. 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.

2. 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.

3. 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])
}
)
)

1. In this measure, FilterTable[Type1], FilterTable[Type2], and FilterTable[Type3] correspond to the three types in your filter table.

2. 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.

[delphinecarnet]

That doesn't make much sense. This formula just return the total of all hours as it adds up all 3 type. I doesn't filter by another table.

I feel we are loosing sight of the end game: it is to filter the SUMMURIZE table and now we are filtering the database which does not filter the SUMMARIZE table.