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

[anshusaketsingh]

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 -

[ypannnn]

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!

[v-jiewu-msft]

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]

Thanks a lot for your response @v-jiewu-msft 

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