Forum Discussion

anshusaketsingh's avatar
anshusaketsingh
Frequent Visitor
2 years ago

Dynamic sorting for matrix column headers based on Descending order of Measure

Hi Experts 

 

So I am using a field parameter as column and want it to sort dynamically based on distinct count of order ids. For eg, I want the below table to sort dynamically with Standard Class on left as it has highest order ids , followed by Second Class, First Class and then Same Day.

 

But it should dynamically change order based on order ids when I switch to Category or any other parameter.

I tried to create a dax to countd distinct ids and rankx to rank the dimensions but it is giving circular error.

Any help will be deeply appreciated.

Added a workbook with case scenerio for reference.

 

Superstore Reference Workbook.pbix

 

Data Preview -

 

Need Help
Show and Tell

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi anshusaketsingh ,

    The link can’t be opened.

    Please try the following methods:

    1.Create the simple table.

    2.Create the new column to sort desc.

     

    Desc order = 
VAR stat = 'Table'[State]
RETURN
RANKX(
    FILTER('Table', 'Table'[State] = stat), 'Table'[ID], ,DESC, Skip
)

     

    3.Drag the field into the matrix visual. Select the Class field, and choose sort by column feature and select new column.

    4.The result is shown below.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • anshusaketsingh's avatar
      anshusaketsingh
      Frequent Visitor
      2 years ago

      Thanks a lot for your response Anonymous 

      The solution unfortunately doesn't seem to wirk because the ids are not unique as in your with my workbook.

      Also its parameter rather than column that I am using.

      Below is how my original data looks like -

       

      Attached the workbook too-

      Superstore Reference Workbook.pbix

       

       

  • ypannnn's avatar
    ypannnn
    Frequent Visitor
    1 year ago

    Hi anshusaketsingh. Did you find a solution? I have encountered a similar issue and would really appreciate it if you could share how you solved it. Thank you!