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