How to add a dynamic index in a table

[MKumar_17]

Hi ,

I have added a series values field with the help of transform data. As shown in below snapshot :

But it is not showing correct asending order once I used this index no in a table with respect to slicer filter

If required adding workbook link:

https://github.com/mohitkumar-Tyagi/xyz/blob/main/using%20all%20in%20sample%20superstore.pbix

Thanks!

[Anonymous · Accepted solution]

Hi @MKumar_17 ,
Based on your description, you want to sort the visualized data while preserving the current filters. You have created an index column in the power query, which just adds a fixed column to the data that cannot be influenced by the slicer. As zenisekd  said, you can create a MEASURE, which will enable dynamic adjustments. For the expression you provided, here is the revised version

Rank = 
RANKX(
    ALLSELECTED(Superstore_Orders),
    CALCULATE(SUM(Superstore_Orders[Sales])),
    ,
    ASC,
    Dense
)

Final output

Best regards,
Albert He

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

[zenisekd]

Hi,

if I understand your problem correctly, you want to index the rows in your visual, not in your table. This means that you need to create a measure, not a calculated column.

The calculated column calculates and adds a fixed index to your table (and saves it to the memory). This index wont be influenced by any slicer. 

You need to create a measure (some kind of rank that would fit your requirements) and apply it to the visual.

[MKumar_17]

@zenisekd , while creating measure, which field we we take inside rankx(allselected(table),calculate(sum(),,asc,dense). not sure which field we will take inside sum() function.

if possible can you make this dax measure correct. May be I am missing something.

[zenisekd]

You need to decide, based on what do you want rank/index the rows. If you use sum() function, you choose a summarized column, based on which you want to rank it. Say that you want the index (thus rank) be running by sales. So you add the sales column in there.