Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
HaNPT
Frequent Visitor

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

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.

4 REPLIES 4
HaNPT
Frequent Visitor

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
New Member

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
New Member

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.

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.