Forum Discussion

ELW's avatar
ELW
Advocate II
5 years ago
Solved

Sort Matrix Columns by Measure or Duplicated Fields

I'm trying to sort columns in a Matrix visual.  The trick is I need to sort by values that differ depending on what's selected in a slicer.  

In this simplified example, I would like the columns (Company AAA, BBB, and CCC) to get sorted by the values for Attribute A.  So if "USA" is selected, it would be BBB, AAA, CCC and if "Mexico" is selected the order would be CCC, BBB, AAA.  I've added a rank column that reflects the order I'd like, based on the values for Attribute A.  

I can't just sort the Company colulmn by Value, since there are multiple Values for each Company (one for each region).  I tried a measure that correctly calculates the desired sort order, but that doesn't seem to be a valid option for sorting the Company column.

 

Is there a trick or workaround for this?  It seems like it should be simple!  I can't figure out how to attach a sample Power BI file, but if I figure it out I'll attach that as well.  

 

ETA: Here's the sample file in Google Drive hopefully that works.

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi ELW ,

     

    The option I see is to add a condittional column on your model:

     

     

    Sorting = 
SWITCH (
    TRUE ();
    'Table'[Region] = "Mexico"
        && 'Table'[Company] = "AAA"; 3;
    'Table'[Region] = "Mexico"
        && 'Table'[Company] = "BBB"; 2;
    'Table'[Region] = "Mexico"
        && 'Table'[Company] = "CCC"; 1;
    'Table'[Region] = "USA"
        && 'Table'[Company] = "AAA"; 2;
    'Table'[Region] = "USA"
        && 'Table'[Company] = "BBB"; 1;
    'Table'[Region] = "USA"
        && 'Table'[Company] = "CCC"; 3
)

     

     

    Now use this column on the your table has an hierarchy:

     

    If you don't want users to see the 1, 2, 3 just add a blank retangle on top of the visualization

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi ELW ,

     

    The option I see is to add a condittional column on your model:

     

     

    Sorting = 
SWITCH (
    TRUE ();
    'Table'[Region] = "Mexico"
        && 'Table'[Company] = "AAA"; 3;
    'Table'[Region] = "Mexico"
        && 'Table'[Company] = "BBB"; 2;
    'Table'[Region] = "Mexico"
        && 'Table'[Company] = "CCC"; 1;
    'Table'[Region] = "USA"
        && 'Table'[Company] = "AAA"; 2;
    'Table'[Region] = "USA"
        && 'Table'[Company] = "BBB"; 1;
    'Table'[Region] = "USA"
        && 'Table'[Company] = "CCC"; 3
)

     

     

    Now use this column on the your table has an hierarchy:

     

    If you don't want users to see the 1, 2, 3 just add a blank retangle on top of the visualization