DAX Dynamic Sort - PBI Report Builder

[diablo9081]

I've got the following construct going to create a table in DAX in Report Builder:

DEFINE

     _Sort = IF(@SortBy = "Util", "[ARXCNT]", "[TotalCost]")

EVALUATE

     ...

ORDER BY _Sort DESC

This doesn't work, and I'm assuming it doesn't because "[TotalCost]" is a string literal, not a column reference. What would be the syntax required to get a reference to the column? ORDER BY [TotalCost] DESC works just fine. I want to be able to use the value of the @SortBy parameter to control the column being used to sort the table.

[v-dineshya]

Hi @diablo9081 ,

Thank you for reaching out to the Microsoft Community Forum.

In Power BI Report Builder, you cannot dynamically substitute a column name or measure reference with a string like in SQL. ORDER BY requires an explicit reference to a column or measure. You are created a text string ("[TotalCost]") instead of a reference to [TotalCost].

Please refer below code.

DEFINE
MEASURE 'Table'[SortValue] =
SWITCH(
TRUE(),
@SortBy = "Util", MAX('Table'[ARXCNT]),
@SortBy = "Cost", MAX('Table'[TotalCost])
)

EVALUATE
ADDCOLUMNS(
'Table',
"SortValue", [SortValue]
)
ORDER BY [SortValue] DESC

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[diablo9081]

This seems to work great for static tables, but If I perform a groupby and aggregate in the EVALUATE section like below, I don't get the right sorting. Do you know what I would have to update to get the summary table to sort correctly? 

DEFINE
MEASURE 'Table'[SortValue] =
SWITCH(
TRUE(),
@SortBy = "Util", MAX('Table'[ARXCNT]),
@SortBy = "Cost", MAX('Table'[TotalCost])
)

EVALUATE
TOPN(

10,

SUMMARIZECOLUMNS(

'Table'[Group_Code],

'Table'[Description],

"ARXCNT", SUM('Table'[30_Day_Count]),

"TotalCost", SUM('Table'[Reimbursed_Amt])
),
[SortValue], DESC
)
ORDER BY [SortValue] DESC

[v-dineshya · Accepted solution]

Hi @diablo9081 ,

Thank you for the update. As you mentioned in your previous response,  you want to  perform a groupby and aggregate in the EVALUATE section like in the above DAX code to get the summary table to sort correctly.

Please try below two options.

1.  You need to compute SortValue inside the SUMMARIZECOLUMNS itself so that it’s aligned with the aggregated row values.

EVALUATE
TOPN (
10,
SUMMARIZECOLUMNS (
'Table'[Group_Code],
'Table'[Description],

"ARXCNT", SUM ( 'Table'[30_Day_Count] ),
"TotalCost", SUM ( 'Table'[Reimbursed_Amt] ),
"SortValue",
SWITCH (
TRUE(),
@SortBy = "Util", SUM ( 'Table'[30_Day_Count] ),
@SortBy = "Cost", SUM ( 'Table'[Reimbursed_Amt] )
)
),
[SortValue], DESC
)
ORDER BY [SortValue] DESC

2. If you want measure, keep the DEFINE MEASURE to rewrite it using SELECTCOLUMNS or ADDCOLUMNS after the aggregation.

DEFINE
MEASURE 'Table'[SortValue] =
SWITCH(
TRUE(),
@SortBy = "Util", SUM ( 'Table'[30_Day_Count] ),
@SortBy = "Cost", SUM ( 'Table'[Reimbursed_Amt] )
)

EVALUATE
TOPN (
10,
ADDCOLUMNS (
SUMMARIZECOLUMNS (
'Table'[Group_Code],
'Table'[Description]
),
"ARXCNT", SUM ( 'Table'[30_Day_Count] ),
"TotalCost", SUM ( 'Table'[Reimbursed_Amt] ),
"SortValue", [SortValue]
),
[SortValue], DESC
)
ORDER BY [SortValue] DESC

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @diablo9081 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @diablo9081 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @diablo9081 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Irwan]

hello @diablo9081 

i think you can create index using RANKX then i sort the visual based on that index.

otherwise, please share share your sample data (remove any confidential information).

Thank you.