Forum Discussion
Helper IIIMeasure to show all rows in table visual when other table doesn't have those values
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!
4 Replies
bernateThis is exactly what I was looking for, thank you!
- lbendlin
Super User
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - 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.
