Forum Discussion
Remove/Hide Blank/Empty Columns in Matrix
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?
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
6 Replies
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.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.
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?
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.
Hello OwenAuger I was inspired reading your response on this question and to adapt it to work for my similar situation. I understand the solution works when we have only measures in the values however in my specific case, I was trying to make a matrix with double header of Group in columns using the field parameter as measures by reading your article on
"Dissecting Field Parameters". Now Power BI created multiplication of measures depending upon the number of groups I have created for the header like below. Is there a way to tweak the Top N Funtionlity so only the relevant measures and group show up ?
Measure Parameter = {
("Sales Amount", NAMEOF('Sales'[Sales Amount]), 0,"Sales"),
("Sales to USA", NAMEOF('Sales'[Sales to USA]), 1,"Sales"),
("Sales to UK", NAMEOF('Sales'[Sales to UK]), 2,"Region"),
("Sales to Australia", NAMEOF('Sales'[Sales to Australia]), 3,"Region")
}
I would really appreciate if you could take sometime on this request.
Hi jats1002
Could you create a mock-up of how you want the visual to look?
When using a field parameter to determine the measures to be included in a visual, it is the same as if you had placed the underlying measures into the field well.
Because of how the matrix visual displays measures, there is no way to display different sets of measures under different column groupings.
I suspect that either a calculation group or switch measure will be more suitable than field parameters in this case, because both of those methods use the values in a column to represent measures, with only a single measure being used in the visual, which allows blank "measures" to be hidden.
- Create a calculation group with a calculation item for each measure
