cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sataylor Frequent Visitor
Frequent Visitor

blank columns in matrix

i am trying to create a risk heatmap using the matrix visualization but need it to show all the columns and rows even if the data has been filtered by the viewer such that there is no data for one of the columns or rows. i.e. the matrix should always be 5x5 even if its filtered down to a site with data in say only 4 rows. I've tried toggling "show items with no data" but this doesn't seem to change the outcome.  any ideas?

6 REPLIES 6
Phil_Seamark Super Contributor
Super Contributor

Re: blank columns in matrix

Sounds like you might need to create a calucated measure that you should use in the Values area of the dropdown, and make sure it always returns a 0 rather than a blank()


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

Re: blank columns in matrix

There's 2 things. One is to show rows that are not filtered but have no data. That is the setting you have referred to. But you seem to be saying something different. How do you see it working?  It sounds like you are saying you want the user to filter, but to still show the rows. That can happen provided the filter doesn't actually filter the row in question. E.g. The filter means the rows are empty but not removed. So I guess it depends on the data model you have. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
sataylor Frequent Visitor
Frequent Visitor

Re: blank columns in matrix

I am showing risks across a number of sites so what i would like to have is a matrix that stays at a fixed size (ie 4 rows and 5 columns)regardless of the data being plotted. when i plot all my data, like in the first clip below there is at least 1 data point in each row and column so they are all visible but if it sliced to a subset of the data some rows or columns don't have any data so are not shown, like in the second clip but i would like to show those on the same 4x5 matrix as the total dataset

 full matrix.JPG

 

single site.JPG

v-yulgu-msft Super Contributor
Super Contributor

Re: blank columns in matrix

Hi @sataylor,

 

In this scenario, my first thread is to create a dynamica column value using measure according to slicer selection. For example, if we don't select "High", then, all the data values corresponding to "High" will be set to Blank, others keep original values. But a measure returns one scalar value each time, and it show data dynamically according to context. So, it is not allowed to specify a special value for those rows where [Probability] equals "High".

 

Another thing you should know is that Blank will not be displayed in visual. The entire row in which [Probability] equals "High" will be hidded. In order to show this row, we have to set " " (space) for data values, but this will change the data type to text automatically.

 

In conclusion, this requirement cannot be achieved right now.

 

Best regards,
Yuliana Gu

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

Re: blank columns in matrix

How about writing a measure that returns zero (instead of blank) in the case there is no value to return?  I think this will do what you want. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
ferdsjoseph Regular Visitor
Regular Visitor

Re: blank columns in matrix

Hi Phil,

 

Is there a way to hide or collapse blank columns in a matrix visual?  Thanks!

Matrix visual with blank columns.png

 

 

Best regards,

 

Ferdinand

 

 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,592 guests
Please welcome our newest community members: