Remove/Hide Blank/Empty Columns in Matrix

[diablo908]

Is it possible to remove or hide empty columns in a matrix? Depending on the filtering in my slicers, some of my measures may not return any data for my selections. I'm not using the columns section of the matrix, and I've instead created measures and added them to the values section. To clean up the matrix, I'd like to only show measure columns (values) that return data.

Power BI does a great job of removing blank rows, but can it somehow also remove blank measure columns?

[OwenAuger · Accepted solution]

Hi @diablo908 

A couple of options for you (sample PBIX attached):

1. Create a calculation group with a calculation item for each measure
   1. Each calculation item expression is just a measure reference.
   2. Place the calculation item column in columns and an arbitrary measure in Values.
2. Create a field parameter table "Measure Parameter" containing each measure.
   1. 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.
   2. Add a TopN visual-level filter set to "Top 1 Measure Parameter by Measure Parameter Flag".
   3. 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?

[diablo908]

The field parameter solution works for me as I have more measures I need to add to the right of those that may have blanks.

[rajendraongole1]

Hi @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.

[diablo908]

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.