Trying to get persistent columns and rows when using a slicer

[robertdpittman]

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.

[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.

[robertdpittman]

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 · Accepted solution]

[robertdpittman]

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.

[robertdpittman]

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.

[SamD02]

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.

 

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins