How to sort by different measure in different table?

[YunJ]

Hi,

I have two tables. I want the "Store" in Table2 can be sorted by the "MS Rank_CM" ("Store" in Table1 also follow this sort.)

I tried "Sort by column" for the "Store", but it does not allow me to choose...

Thanks a lot!

[amitchandak]

@YunJ , if Table1 Is kind of master to table 2 then you can create a sort order in table 1 and use that with table 2.

else create a common dimension table and create the CM rank there and use that as sort column for the store in dim and use that.

[YunJ]

Hi @amitchandak 

Thanks for your help.

But I already add a sort for DimStore which is dynamic. But I cannot sort Store by this.. May be it is a measure not a column?

[Anonymous]

HI @YunJ,

You can consider creating a table with your measure names, then you can use it as the source of slicer and write a measure formula with switch function to check the current selection and dynamic return ranking expressions.

Calculate table:

Selector =
SELECTCOLUMNS (
    { "Measure1", "Measure2", "Measure3", "Measure4" },
    "Name", [Value]
)

Measure:

Dynamic Rank =
VAR selected =
    SELECTEDVALUE ( Selector[Name] )
RETURN
    SWITCH (
        selected,
        "Measure1", 'rank expression1',
        "Measure2", 'rank expression2',
        "Measure3", 'rank expression3',
        "Measure4", 'rank expression4',
        'rank expression1'
    )

Regards,

Xiaoxin Sheng

[YunJ]

Hi @Anonymous 

I'm sorry I don't quite understand. I only have one measure (Rankx I wrote before) to sort both two tables. But I don't want to put this measure into Table2, only the sort order of Table2 should follow this measure.. and the measure is dynamic, which means it will changed by the slicer YearMonth.

Thanks

[AllisonKennedy]

@YunJ  Sort by column only works with COLUMNS (not measures): 

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

Your STORE table only has 1 column, so you cannot use Sort by column.

You can simply sort your matrix by the MEASURE instead as you have done, otherwise you will need to add a sorting COLUMN (which will not be dynamic as your measure is).

[YunJ]

Hi @AllisonKennedy 

I only have one measure (Rankx I wrote before) to sort both two tables. But I don't want to put this measure into Table2, only the sort order of Table2 should follow this measure.. and yes, the measure is dynamic, which means it will changed by the slicer YearMonth. 

This is a monthly report, so maybe I should add a sort order column in the DimStore per month, and sort manually...

Thanks for your help.

[AllisonKennedy]

@YunJ  Sort by column only works when the columns are in the same table. You'll need to create a DimStore table and use that in all your visuals, slicers, etc. That DimStore table needs to have at a minimum:

1) Store ID

2) Store Name

3) Rank/Sort Order

Then connect it to the other tables using the ID and ONLY use the DimStore[Store Name], never use Store Name from any other table in your visuals. 

Since your model is in chinese I can't help much more, but it looks like you have a start on the DimStore table with Store, but you need to connect that still to MU_red, and add the Rank/Sort order to that table. 

[YunJ]

Hi @AllisonKennedy 

I already create a rank measure for the Dimstore, but it is dynamic (changed by the slicer YearMonth). So I'm not sure how to add rank order...

Also, I cannot connect Dimstore to MU_red, since in MU_red data it doesn't contain Store...

Thanks for your kindly help.