Forum Discussion
Show items with no data: show items from one table based on data (no data) from other table
Hi,
Please consider following senario:
Table 1
| Name | ID |
| A | 1 |
| B | 2 |
| C | 3 |
| D | 4 |
| E | 5 |
| F | 6 |
| G | 7 |
| H | 8 |
Table 2
| Date | ID | Hours spent |
| 26-09-2016 | 1 | 1 |
| 26-09-2016 | 1 | 1 |
| 26-09-2016 | 2 | 1 |
| 26-09-2016 | 3 | 1 |
| 27-09-2016 | 1 | 1 |
| 27-09-2016 | 2 | 1 |
| 27-09-2016 | 3 | 1 |
| 27-09-2016 | 1 | 1 |
| 27-09-2016 | 2 | 1 |
| 27-09-2016 | 3 | 1 |
| 28-10-2016 | 1 | 1 |
| 28-10-2016 | 2 | 1 |
| 28-10-2016 | 3 | 1 |
Relation between table 1 and table 2 is --> 1 to many relation, both directional
Based on date from table 2, I have 2 slicer week and year.
When I select week 40 and year 2016, I get following output:
| Name | Hour Spent |
| A | 5 |
| B | 4 |
| C | 4 |
My desired output is:
| Name | Hour Spent |
| A | 5 |
| B | 4 |
| C | 4 |
| D | |
| E | |
| F | |
| G | |
| H |
for remaining employee who has no hours in table 2, either it should be 0 or null. The problem is we are not having any data for other names for those dates in Table 2 thats why it is not showing name, even if I enable show items with no data than also I am not able to get these name.
I was trying to do something like allexcept where we can simply ignore the week slicer for display but not getting how to do it.
To summurise my problem in one line:
I want to display the name of all the employee and their hours spent details (where it is 0 or some value)
Please suggest possible solution.
First, bravo for posting a clear explanation of the problem and sample data that is not a screen shot and thus has to be manually entered in order to replicate the problem. Nice job.
What you want is this Measure in your Names table:
MyMeasure = IF(SUM(HoursSpent[Hours spent])>0,SUM(HoursSpent[Hours spent]),0)
4 Replies
First, bravo for posting a clear explanation of the problem and sample data that is not a screen shot and thus has to be manually entered in order to replicate the problem. Nice job.
What you want is this Measure in your Names table:
MyMeasure = IF(SUM(HoursSpent[Hours spent])>0,SUM(HoursSpent[Hours spent]),0)
Thank you Greg_Deckler,
It solved the part of my problem now instead of getting blank I am getting 0 but I am still not getting the name of those employee who has not put any hours in Table 2.
I don't understand, if I create a table visualization with [Name] and [My Measure], I see all the names including the ones that have 0. Are you seeing something different or are you using a different visualization?
