Forum Discussion
Create a table visual showing items with no data
Assume that I have a list of doors, and I assign a unique name to each door – I will call the list of doors my inventory.
Each door has a sensor that registers the time someone passed through the door.
I can easily generate a report that will show me how many times someone passed through each door.
However, that report will not include the doors that were not used.
I would like to generate a report that will list the doors that nobody passed through them.
I created a table when column 1 is the door number and column 2 is the number of times a door was passed. I also seleted the "show items with no data" option for the table (per https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data). However, even with this option selected Door3 does not show.
Here is an overly simplified example. I have a room with four doors:
As such, my “inventory” is Door1, Door2, Door3 and Door 4.
The doors sensors gave me the following information:
Door1 – 8/5/2024 08:01:02
Door2 – 8/5/2024 08:03:02
Door2 – 8/5/2024 08:04:22
Door4 – 8/5/2024 08:10:33
Door1 – 8/5/2024 08:10:34
Door1 – 8/5/2024 08:12:55
Door4 – 8/5/2024 08:30:21
Door2 – 8/5/2024 08:31:45
Door2 – 8/5/2024 08:33:11
Any ideas how to make it to work?
Thanks for the reply from Greg_Deckler , please allow me to provide another insight:
Hi, reshefy
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.Create calculated table references:
Table2 = VAR T2 = SELECTCOLUMNS ( 'Table', "Value", LEFT ( 'Table'[information], 5 ) ) VAR T3 = { "Door1", "Door2", "Door3", "Door4" } RETURN EXCEPT ( T3, T2 )3.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
reshefy You are going to need a, probably, disconnected table that lists all of your doors. I just solved a similar issue here:
Thanks for the reply from Greg_Deckler , please allow me to provide another insight:
Hi, reshefy
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2.Create calculated table references:
Table2 = VAR T2 = SELECTCOLUMNS ( 'Table', "Value", LEFT ( 'Table'[information], 5 ) ) VAR T3 = { "Door1", "Door2", "Door3", "Door4" } RETURN EXCEPT ( T3, T2 )3.Here's my final result, which I hope meets your requirements.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the solutions. Maybe I was not clear enough in my posting - I appologize for that.
For the sample data that I provided, I would like to show the folloing table:
Door | number of events
----------------------------
Door1 | 3
Door2 | 4
Door3 | 0 (or blank if it is easier)
Door4 | 2
I would also like the be able to add a date slicer, do when I select a date range, only the events during that date range will be counted (i.e., if my sample data had data from 8/6/24 and I select just 8/6, only 8/6 data will show, but the table wil lstill list the four doors, regardless how many of them triggered an event.
Thank you,
Yaron
Regarding the disconnected tables - I am not sure I understand how to link my data to teh disconnected table. I also use the same table for other visualizations, so if I figure out how to link teh disconnected table, I will need to import the data in that table twice...
Thank you,
Yaron
