How to sort matrix by a measure but not show value of that measure when exporting data

[HaNPT]

Hello,

I'm having a requirement to sort a matrix by a custom column named "Sort by", but the users don't want to see the value of this measure when they export data.

"Sort by" is a custom measure created to dynamically sort the matrix by user selection.

Dax code is

SWITCH(SELECTEDVALUE('Sort By'[Sort By]), 
"Sales" , [Sales],
"Cost" , [Cost],
"Revenue" , [Revenue])

User can select from a slicer which metric they want to sort the matrix (Sales/ Cost/ Revenue).

I can hide the value of "Sort by" in matrix visual, but cann't find any way to hide value in export data. Please help.

[HaNPT]

Hello everyone,

Is there anyone has new idea for my issue? It's required to export data from the matrix, but the Sort By value must be 0 or hide from user.

[shop-tinh-yeu]

By doing this, the exported data won't include the values of the Sorting Measure, as you're using a separate table for export that doesn't include this measure.

Remember to adjust the column names and table references according to your actual data model. This approach allows you to control what gets exported while still using the Sorting Measure for sorting in the matrix visual.

[shop-tinh-yeu]

Hello,

To achieve your requirement of sorting a matrix by a custom column ("Sort by") and not displaying the measure value in the exported data, you can follow these steps:

1. Create a Sorting Measure:

   • Define a new measure to handle sorting based on user selection.
   DAXCopy code

   Sorting Measure = SWITCH ( SELECTEDVALUE ( 'Sort By'[Sort By] ), "Sales", [Sales], "Cost", [Cost], "Revenue", [Revenue] )

2. Hide the Sorting Measure in the Matrix Visual:

   • In the matrix visual, you can hide the Sorting Measure by setting its formatting options to not show in the visual.

3. Create a Dummy Table for Export:

   • Create a new table or use an existing one that contains the data you want to export. Include the necessary columns, but exclude the Sorting Measure.
   DAXCopy code

   ExportTable = SELECTCOLUMNS ( YourBaseTable, 'Other Column 1', 'Other Column 2', ... )

4. Export the Dummy Table:

   • When users export data, they will be exporting the dummy table without the Sorting Measure.

[HaNPT]

Thank you for your solution, but my user used to export data directly from the matrix, is there any other way to achieve the requirement with data exported from matrix?

And if I create another table for export, the data will not be sorted as desired when not including the Sort By measure.