Measure help against month filter

[Morkil]

I have a table in my power bi report that lists departments against how many hours they have worked on a construction job. It uses a field for Department and Hours Worked. The users can filter the report by month to see how many hours each department worked on the construction job (e.g., painting did 84 hours this month).

My issue is that when the user filters to a month where a particular department have done no hours in, the table doesn't return these departments at all and instead just returns the departments that have hours against them.

How can I write a measure to ensure that even the departments with no hours (are null) are included in the table. I'd like to make these show up as 0 hours.

[123abc · Accepted solution]

To ensure that departments with no hours in a filtered month are included in the table and show up as 0 hours, you can create a measure that calculates the hours worked for each department in the selected month and handles cases where there are no hours worked. You can use the following DAX (Data Analysis Expressions) measure in Power BI:

Total Hours =
VAR SelectedMonth = SELECTEDVALUE(Calendar[Month]) -- Replace 'Calendar' with your actual date table
RETURN
IF(
ISFILTERED(Calendar[Month]), -- Check if a month filter is applied
SUMX(
FILTER(ALL('YourTable'), 'YourTable'[Month] = SelectedMonth),
'YourTable'[Hours Worked]
),
SUM('YourTable'[Hours Worked]) -- If no filter is applied, show total hours for all departments
)

Here's how this measure works:

1. It first checks if a month filter is applied using the ISFILTERED function.

2. If a month filter is applied, it calculates the sum of hours worked for each department in the selected month using SUMX and FILTER.

3. If no month filter is applied, it calculates the total sum of hours worked for all departments.

This measure will ensure that even departments with no hours in the selected month will be included in the table and displayed as 0 hours when no data is available for that specific month.

Make sure to replace 'YourTable' with the actual name of your table containing department and hours worked data, and 'Calendar' with the name of your date table that contains the month filter.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.