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

show all dim records, even with no fact data, but keep filters

I have a list of persons who are assigned to sites.
For each day, i want to see what persons were onsite and what persons were not.

 

I need to be able to do this while still filtering by site. i.e. i only see persons for the selected site.

The issue i have is if i create a measure to count a users records, users with no records will not be present.
If i wrap that measure in an IF, so it assigns 0 if there is no record, i see all persons for all sites, not a problem here, but in my real dataset with thousands of persons, it is a big issue.


How can i see all persons for the select site, and if they were present on a paritcular day or not?

Below is a screenshot of sample data and desired result if i had selected site A, and a link to a pbix containing this sample data. Appretiate any expertise provided.

https://1drv.ms/u/s!AgldA0VQfPV9hNtsompxszevR8TRTA?e=viRG1j 

 

 

Capture.JPG

 

1 ACCEPTED SOLUTION

hi  @wilson_smyth 

You could use this measure as below:

Records = 
IF(CALCULATE(MAX('Fact'[personid]))<>BLANK()&&MAX(DimPerson[assigned site]) in VALUES(DimSite[siteID]),1,IF(MAX(DimPerson[assigned site]) in VALUES(DimSite[siteID]),0))

Result:

1.JPG 

 

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Super User II
Super User II

Hi @wilson_smyth 

I have changed the relationships and get the following result with your attached pbix file (see below):

 

30-09-_2020_15-15-05.png

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

thanks. thats a help, and its helped me refine my question!

This shows persons with no records, but if i select a date, i only see people on that date. On any given day, i need to see the people who were onsite and the people who were not. An indicator measure would work, i.e. 1 if they were onsite, 0 if they were not. 

Will play with and build on the idea you provided.

Thanks!

hi  @wilson_smyth 

You could use this measure as below:

Records = 
IF(CALCULATE(MAX('Fact'[personid]))<>BLANK()&&MAX(DimPerson[assigned site]) in VALUES(DimSite[siteID]),1,IF(MAX(DimPerson[assigned site]) in VALUES(DimSite[siteID]),0))

Result:

1.JPG 

 

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors