Forum Discussion
Remove/Hide Blank/Empty Columns in Matrix
Hi diablo908
A couple of options for you (sample PBIX attached):
- Create a calculation group with a calculation item for each measure
- Each calculation item expression is just a measure reference.
- Place the calculation item column in columns and an arbitrary measure in Values.
- Create a field parameter table "Measure Parameter" containing each measure.
- Create a measure "Measure Parameter Flag" that checks whether the measure corresponding to the "current" field parameter item is nonblank, and if so return 1.
- Add a TopN visual-level filter set to "Top 1 Measure Parameter by Measure Parameter Flag".
- This method relies on it being sufficient to check that the "total" of each measure is nonblank to decide whether to include it in the matrix.
Do either of these methods work for you?
- Create a calculation group with a calculation item for each measure
Super UserHi diablo908 - Yes, you can remove or hide empty measure columns in a Power BI matrix when they return only blank values. Since Power BI automatically removes rows with all blanks but does not remove blank measure columns by default, you need to use a workaround.
Ensure that your measures return BLANK() instead of 0 when they should be hidden. Power BI automatically removes columns where all values are BLANK().
Measure_New = IF( SUM(Sales[Amount]) = 0, BLANK(), SUM(Sales[Amount]) )
If you have multiple measures and want to dynamically remove blank one
Create a new table with a list of your measures.
Use a Disconnected Table approach with a slicer.
Apply logic to only show non-blank measures.
This doesn't seem to work in practice as far as I can tell. I even tried Measure_New = BLANK() to force all blank and the column is still there.