Sort order with multiple columns of slicer

[Anonymous]

Hi ,

im trying to do sort order dynamic by slicer selected columns,

ex:

my table has columns tbl(A,B,C,D,E) want to do sorting dynamically by choosing these from slicer and sort.

can some one give me DAX query.

i dont want this @PowerQuery because im using directquery want it to be dynamic slicer selection basis.

want to Use Summarize,Allselected,Order By (A,B) or Order by (C,D) etc

_DynamicSlicerSelected Sorting=

SUMMARIZE(myTbl,
myTbl[A],
myTbl[B],
myTbl[C],
myTbl[D]
)ORDER BY Slicer SelectedValues

[v-juanli-msft · Accepted solution]

Hi @Anonymous

Here is a workaround.

1. create two tables by "enter data", please see "Table1" and "Table2" in my pbix.

Note, don't create any relationships among these tables.

2. create measures in your data table

in my test

Measure_a = MAX([A]) 

Measure_b = MAX(Sheet9[B])

Measure_c = MAX([C])

Measure_d = SUM([D])

rank_a = RANKX(ALL(Sheet9),[Measure_a],,ASC)

rank_b = RANKX(ALL(Sheet9),[Measure_b],,ASC)

rank_c = RANKX(ALL(Sheet9),[Measure_c],,ASC)

rank_d = RANKX(ALL(Sheet9),[Measure_d],,ASC)

SWITCH1 =
IF (
    HASONEVALUE ( Table1[measure_name1] ),
    SWITCH (
        MAX ( Table1[measure_name1] ),
        "rank_a", [rank_a],
        "rank_b", [rank_b],
        "rank_c", [rank_c],
        "rank_d", [rank_d]
    )
)


SWITCH2 =
IF (
    HASONEVALUE ( Table2[measure_name2] ),
    SWITCH (
        MAX ( Table2[measure_name2] ),
        "rank_a", [rank_a],
        "rank_b", [rank_b],
        "rank_c", [rank_c],
        "rank_d", [rank_d]
    )
)

rank_by_selected = [SWITCH1]&" "&[SWITCH2]

then click on the "arrow" on the header of [rank_by_selected].

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stachu]

is this supposed to be calculated column? if so then it will not work, as they are static in a sense that they precalculate with the model refresh and do not change with the slicer selection
you can see the 3rd paragraph of this article describing the difference between column and measure
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

if this is supposed to be a measure - how are you planning to use it exactly? you'd probably need SWITCH to cover the selection from the Slicer

[Anonymous]

Thanks stachu for reply,

let this be a measure then,with MIN(any field) bringing selection with switch is not a problem.

but SUMMARIZE or SUMMARIZECOLUMNS not working with ORDER BY Clause.

[Anonymous]

SUMMARIZE ro SUMMARIZECOLUMN  is Table valued function so,here there is no point whether it is a measure or Calculated column.

im just simply try to achieve below SQL in DAX.

ex:

select * from mytable order by A,B asc columns--default in table

after selecting table my order by should change as per selected columns.

if i select B,C in slicer then need to rewrite it dynamic.

[Stachu]

your SQL is going to return a table, right?

the only way to achieve a dynamic table in DAX is to do it within a measure (e.g. with SUMMARIZE), but measure will always return a scalar value, so single number or text

there is no way in DAX to have a dynamic calculated column/calculated table that changes with slicers selection