Measure to show all rows in table visual when other table doesn't have those values

[bernate]

Hello! I have 2 tables: Employees and Hours. I am trying to make a table visual to show Employees and their Hours Worked. 

Emplopyees are connected to Hours with Many-to-One on Employee ID, with many Employee IDs on the Hours table and no duplicate Employee IDs on the Employee table. 

Not every Employee has worked hours, so they will not appear on the Hours table. When I make a table visual, I add the Employee Name column from the Employee table and have 17 employees. I then add the Hours column from the Hours table, and my employee count drops to 9 because I have 8 employees who have not worked hours. 

I would still like to show those 8 employees on the table visual, but either with a 0 or Blank in the hours column. I know I can add a calculated column to the Employees table summing the hours from the Hours table, but can this be done with a measure instead? I tried IF(SUM('Hours'[Hours])=BLANK(),0,SUM('Hours'[Hours])), but this returns all employees and ignores the slicers I have on the page.

Thank you!

[danextian · Accepted solution]

Hi @bernate 

You can add 0 to a measure to force Power BI to show all rows. Example SUM('Hours'[Hours]) + 0

Or you can enable Show items with no data

[bernate]

This is exactly what I was looking for, thank you!

[Ashish_Mathur]

Hi,

Try this measure

Measure = coalesce(SUM('Hours'[Hours]),0)

If this does not work, then share the download link of the PBI file.  Show the problem there clearly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...