Forum Discussion

diablo9081's avatar
diablo9081
Frequent Visitor
10 months ago
Solved

DAX Dynamic Sort - PBI Report Builder

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  ...
Need Help
  • v-dineshya's avatar
    v-dineshya
    10 months ago

    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

     

diablo9081's avatar
diablo9081
Frequent Visitor
10 months ago

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's avatar
v-dineshya
Community Support
10 months ago

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's avatar
    v-dineshya
    Community Support
    10 months ago

    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's avatar
      v-dineshya
      Community Support
      10 months ago

      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's avatar
        v-dineshya
        Community Support
        10 months ago

        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