cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

 

NameID
A1
B2
C3
D4
E5
F6
G7
H8

 

 

Table 2

 

 

DateIDHours spent
26-09-201611
26-09-201611
26-09-201621
26-09-201631
27-09-201611
27-09-201621
27-09-201631
27-09-201611
27-09-201621
27-09-201631
28-10-201611
28-10-201621
28-10-201631

 

 

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:

 

NameHour Spent
A5
B4
C4

 

My desired output is:

 

NameHour Spent
A5
B4
C4
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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

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

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)

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

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

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)

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

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

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.

 

 

 

 

Super User
Super User

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

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?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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

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.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 3,265 guests
Please welcome our newest community members: