Forum Discussion
Show records with missing data
Hello,
I have an interesting issue that I couldnt find the answer for directly on the forums. Apologies if it has been answered in the past and I've missed it.
I have a simple table as below:
| PersonID | Date | Check |
| 1 | 01/01/2016 | 1 |
| 1 | 04/08/2017 | 2 |
| 1 | 08/08/2017 | 4 |
| 1 | 12/03/2018 | 5 |
| 2 | 05/06/2016 | 3 |
| 2 | 07/08/2017 | 4 |
| 3 | 06/07/2018 | 1 |
| 4 | 08/10/2017 | 1 |
| 4 | 04/03/2016 | 2 |
| 4 | 06/09/2017 | 3 |
| 4 | 12/11/2018 | 5 |
I would like to slicer select 'Check 4' and display all PersonIDs and their record. If the personID does not have a 'Check 4', there record should still show but it should show as null, indicating that it is missing.
I tried creating a distinctcount measure on the PersonID and then filtering it that way but it didn't seem to work.
Cheers,
cmob
Hi Anonymous ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , Create a measure like this and try
Distinctcount(Table[PersonID]) +0
Use this with PersonID in visual and use slicer filter on check
Hi Amit,
Thanks for the prompt reply as always. I've tried what you've suggested and created a table that has: DistinctCountPersonID+0, Date, Check
I then used check in a slicer. However, instead of displaying all results with 0's in the date and check for missing checks on that ID, it only shows me the IDs with the check. In my original example, if I used this method and filtered for 'Check 4', I would only see PersonID 1 & 2 where as PersonID 3 & 4 should still show but with 0 or null values in Date/Check.
Apologies if I've misunderstood your suggestion!
Hi Anonymous ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
