Forum Discussion
Show selected calculation group items with no data, but not rows where every calculation item is 0
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.
Super UserYou 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.Hi champioz,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Community Support Team.Hi champioz,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Regards,
Community Support Team.