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

 

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.

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

     

7 Replies

  • Irwan's avatar
    Irwan
    Super User
    10 months ago

    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.

  • v-dineshya's avatar
    v-dineshya
    Community Support
    10 months ago

    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'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