How to sort the slicer which is from the table made by DAX?

[YunJ]

Hi,

I use DAX to make a dimension table as following as my slicer. But I'm wondering how to sort when showing as the slicer?

[Anonymous · Accepted solution]

Hi  @YunJ ,

If you use the original column to sort by the Index column created with Dax, it will produce a circular dependency.

https://community.powerbi.com/t5/Desktop/Circular-dependancy-error-creating-relationship/m-p/415732

Can you consider Create calculated table, and then sort by the new columns [Beauty Category] and [Index] in Table2.

Table 2 =
SUMMARIZE('Table','Table'[Beauty Category],"Index",
SWITCH(
    TRUE(),
    'Table'[Beauty Category]="Hair",1,
    'Table'[Beauty Category]="Makeup",3,
    'Table'[Beauty Category]="Female Skincare",4,
    'Table'[Beauty Category]="Men",2))

Best Regards,

Liu Yang

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

[YunJ]

@Anonymous  Thanks for your solution. It really helps!

[Anonymous]

Hi  @YunJ ,

You can enter the Power query to customize the sort order of the specified columns

1. Add Column – Column From Examples – From Selection

2. Custom order rules

3. Select the column [Beauty Category], Column tools – Sort by column – [Custom]

4. Result.

The sorting of slicers also changes.

Best Regards,

Liu Yang

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

[YunJ]

Hi @Anonymous 

I cannot find my table in power query because it is a table written by DAX.

Thanks

[mussaenda]

Hi @YunJ ,

you can add an index column and sort your column according to the index.

Hope this helps

[YunJ]

Hi, May I ask how to add an index column at this situation?

[mussaenda]

Hi @YunJ ,

Try the idea from the attached pbix.

Hope this helps

[YunJ]

Hi @mussaenda 

Thanks for your reply. 

In your way I can only use the "column" column as a slicer. I'm wondering is there a way I can use the original "Beauty Category" as my slicer? (Because I already use this column to make relationship and create many measures and visualizations.) 

[Pragati11]

HI @YunJ ,

The slicers get sorted by themselves. Like in the case above, your slicer is sorted in alphabetical order.

Do you want to sort them in a custom order apart from this default alphabetical order?

Thanks,

Pragati

[YunJ]

Hi @Pragati11 I want to sort them in a custom order.