Forum Discussion
Trying to get persistent columns and rows when using a slicer
I want to format a table visualization with columns and rows that are persistent even when all the contents of the cells in the row or column are non-existent. The contents of the table is controlled by a slicer.
For example, if there is a value in each cell, the table looks like this.
| Introduced | Working | Rejected |
Green | 3 | 1 | 2 |
Red | 5 | 2 | 3 |
Yellow | 6 | 5 | 1 |
However, if the there is no data for some of the cells, the table looks like this.
| Introduced | Working |
Green | 3 | 3 |
Yellow | 6 | 6 |
I would like the table to look like this
| Introduced | Working | Rejected |
Green | 3 | 3 |
|
Red |
|
|
|
Yellow | 6 | 6 |
|
|
|
|
|
I have tried various combinations of formatting and I cannot get it to work.
7 Replies
- lbendlin
Super User
To report on things that are not there you need to use disconnected tables and/or crossjoins
You need to select an option of "Show items with no data" .
It is available in the formatting option of visual, select drop-down of your rows section and this option would be visible.
Unfortunately the "Show items with no data" did not work. The visualization is a matrix and the values are the count of the rows in each column.
I placed a sample powerBI file on One Drive
https://1drv.ms/u/s!AkwaMVVWng4DyU4CMNheoh1z9pUs?e=MxTjGY
It illustrates the issue I am having. It does not match my original description exactly but illustrates the issue perfectly.
I have also been looking at changing blanks to zeroes
https://www.youtube.com/watch?v=ED-kVyg51q0
but my limited experience with Power BI is preventing me from getting it to work in this case.
Any help with this is welcome
- lbendlin
This is brilliant. I have not used disconnected tables before. I spent a few hours applying it to my production visulaization and it worked like a charm.
Thanks a million for updating the file and sending it back. It was a huge help.
- Ashish_Mathur
Hi,
I cannot download your file but if you are OK with substituting blank with 0, then drag this measure to your visual
Measure = coalesce([your measure],0)
Hope this helps.
