Forum Discussion
Show selected calculation group items with no data, but not rows where every calculation item is 0
I have a very niche issue.
I am using field parameters and calculation groups to let users build a matrix. They pick measures from the calculation group, a dummy measure in the values field of the matrix passes through the underlying selected measures 1:1 + formatting. Paramters populate column and row headers.
Users want to see a row header for every measure they have selected for items appearing in the matrix. Example:
-Warehouse 1 and Warehouse 2 have some data for either sales or production, so they both appear on the matrix
-Warehouse 1 only has sales data, so Warehouse 1 > Prod Order Qty does not appear on the matrix. Users want this to change
-Crucially, any hypothetical Warehouse 3 with no sales OR production data has to stay off the matrix
Is it possible to populate just these missing measure name row headers or have I designed myself into a corner?
What I have tried:
-Show items with no data shows every warehouse in the list
-COALESCE([Measure], "") or similar in the calculation item crashes, too many items and too intensive
-There are too many measures to do the thing where I contrive a mega filter that includes all items but removes rows where every measure is 0...unless there's some easy way to involve every measure in a filter simultaneously that I'm missing
Hi champioz,
Thank you for reaching out to the Microsoft fabric community forum.
I understand that you want each selected measure to show as a row header for warehouses with at least one populated measure, while hiding warehouses with no data. Power BI’s matrix doesn’t directly distinguish between missing measures for a warehouse and warehouses with no data, so methods like +0, COALESCE, or “Show items with no data” can add extra rows you don’t want. One workaround is to create a helper table listing all warehouse–measure combinations and use it as the row axis, so headers always display but you can still filter out warehouses where all measures are blank. Alternatively, you could add an “AnyDataExists” measure to flag warehouses with any non-blank measure and use that to filter the matrix. While not perfect, this approach avoids issues with COALESCE and gives you more control over which rows are shown.
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Community support Team.
5 Replies
jgeddesSuper User
You may be able to add a "+0" to your measures to get the result you are looking for. Of course it will be depend on the types of measures you have. As an example...
Sales Total = SUMX( FILTER('Table', 'Table'[Value] = "Sales"), 'Table'[Quantity] ) + 0This may cause some unintended consquences when working with date visuals though.
Unfortunately this is very similar in effect to trying to force rows to show blanks like with COALESCE([Measure], ""). It populates every row, including the unwanted ones like Warehouses that have no value for either measure in this example
Hi champioz,
Thank you for reaching out to the Microsoft fabric community forum.
I understand that you want each selected measure to show as a row header for warehouses with at least one populated measure, while hiding warehouses with no data. Power BI’s matrix doesn’t directly distinguish between missing measures for a warehouse and warehouses with no data, so methods like +0, COALESCE, or “Show items with no data” can add extra rows you don’t want. One workaround is to create a helper table listing all warehouse–measure combinations and use it as the row axis, so headers always display but you can still filter out warehouses where all measures are blank. Alternatively, you could add an “AnyDataExists” measure to flag warehouses with any non-blank measure and use that to filter the matrix. While not perfect, this approach avoids issues with COALESCE and gives you more control over which rows are shown.
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Community support Team.
