Forum Discussion
Matrix Visual – Sort by a Measure Without Showing It (Field Parameter in Use)
Hi all,
I’m working on a Power BI report with a matrix visual and need help with sorting.
Visual Setup:
Rows: Project and Region (from ProjectLookup and RegionLookup tables)
Values: Using a field parameter table (ProjectedDeviationSummary) that switches between:
LastWeekProjectedUtilization
ProjectedUtilization
UtilDifference
HeadCount
I want to sort the matrix by HeadCount but not show the HeadCount column in the matrix visual. I tried creating a measure like below.
HeadCount_Sort_Measure =
CALCULATE(
[HeadCount],
'ProjectedDeviationSummary'[ProjectedDeviationSummary Order] = 3
)
Added this measure to the matrix and sorted by it — sorting works. However, there’s no way to hide this measure column in the matrix without losing the sort. If I remove the measure from the values, the sort resets. I also tried returning BLANK() or using conditional formatting (white text), but that still leaves empty space in the matrix.How can I sort the matrix visual using a measure from the parameter table (like HeadCount) without displaying it as a column in the visual?
Is there any workaround — maybe DAX or visual trick — to achieve this?
Any help would be appreciated. Let me know if you'd like me to share a sample PBIX file too. Thanks in advance!
bhanu_gautam Ritaf1983
Hi NagaPushpa ,
The option you have since you are using a matrix is to reduce the size of the column in the matrix until is not visible (like you do in excel) drag and drop at header level, but be carefull that you need to turn off word wrap at column header and values.
Also be carefull that if a user clicks on top of any other column it will sort by that column:
Hi NagaPushpa,
Thank you for reaching out to the Microsoft fabric community forum. Thank you MFelix, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
5 Replies
Hi NagaPushpa ,
The option you have since you are using a matrix is to reduce the size of the column in the matrix until is not visible (like you do in excel) drag and drop at header level, but be carefull that you need to turn off word wrap at column header and values.
Also be carefull that if a user clicks on top of any other column it will sort by that column:
Thanks for your response. But this is not much efficient right. I tried hiding by making the text color and background to white. I see this is not as best. Can you please suggest any other alternative?
Hi NagaPushpa,
Thank you for reaching out to the Microsoft fabric community forum. Thank you MFelix, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.NagaPushpa Hey,
try to create a measure like thisHiddenSortTable =
SUMMARIZE(
'ProjectedDeviationSummary',
'ProjectedDeviationSummary'[Project],
'ProjectedDeviationSummary'[Region],
"HeadCount_Sort", [HeadCount]
)then you can sort the column as per your requirement.
Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your queryThanks HarishKM
I tried using this sorting is working even with the parameter table I created. But the requirement is to hide the column but sorting should be enabled.
