Show items with no data: show items from one table based on data (no data) from other table

[aktripathi2506]

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.

[Greg_Deckler · Accepted 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)

[aktripathi2506]

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.

[Greg_Deckler]

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?

[aktripathi2506]

Sorry @Greg_Deckler, I was having one more condition enabled bacause of that I was getting different values. I changed it.

Now all good, It worked and solved the problem. Thank you for the quick responce.