Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sort the dynamic values in a text box

Hi everyone,

 

I have a a slicer in my dashboard and a text box to show all the values that are selected in the slicer or show 'All' if all values are selected. It is working fine and shows the correct selections except that they are not sorted. Is there any way to sort these dynamic values? I am kind of new to Power BI and have tried to look for some documentation on this with no luck so far.
Hence, any help would be highly appreciated. Thank you in advance for your time!

Here is the calculation I have used for the text box value:

Week Card : =
VAR _countrs =
    COUNTROWS(VALUES('Fiscal_Cal'[Week]))
var _countsall =
    CALCULATE(
            COUNTROWS(VALUES('Fiscal_Cal'[Week])), REMOVEFILTERS('Fiscal_Cal')
    )
    return
    if( _countrows = _countsall, "All",
    CALCULATE(CONCATENATEX(DISTINCT('Fiscal_Cal'[Week]), 'Fiscal_Cal'[Week], ", ")
    ))

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    3 years ago

    Hi Anonymous ,

     

    To sort the dynamic values that are shown in the text box, you can use the SORT function to sort the list of values returned by the CONCATENATEX function. Here is an example of how you can modify your measure to achieve this:

    Week Card : =
VAR _countrs =
    COUNTROWS(VALUES('Fiscal_Cal'[Week]))
var _countsall =
    CALCULATE(
            COUNTROWS(VALUES('Fiscal_Cal'[Week])), REMOVEFILTERS('Fiscal_Cal')
    )
    return
    if( _countrows = _countsall, "All",
    CALCULATE(
        CONCATENATEX(
            SORT(DISTINCT('Fiscal_Cal'[Week]), 'Fiscal_Cal'[Week], ASC),
            'Fiscal_Cal'[Week],
            ", "
        )
    ))

    The SORT function takes in a table as the first argument, and then a list of columns to sort by as subsequent arguments. In this case, we are sorting the list of distinct weeks in ascending order. The ASC argument specifies that the sort order should be ascending. If you want to sort the values in descending order, you can use the DESC argument instead.


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

2 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous ,

     

    To sort the dynamic values that are shown in the text box, you can use the SORT function to sort the list of values returned by the CONCATENATEX function. Here is an example of how you can modify your measure to achieve this:

    Week Card : =
VAR _countrs =
    COUNTROWS(VALUES('Fiscal_Cal'[Week]))
var _countsall =
    CALCULATE(
            COUNTROWS(VALUES('Fiscal_Cal'[Week])), REMOVEFILTERS('Fiscal_Cal')
    )
    return
    if( _countrows = _countsall, "All",
    CALCULATE(
        CONCATENATEX(
            SORT(DISTINCT('Fiscal_Cal'[Week]), 'Fiscal_Cal'[Week], ASC),
            'Fiscal_Cal'[Week],
            ", "
        )
    ))

    The SORT function takes in a table as the first argument, and then a list of columns to sort by as subsequent arguments. In this case, we are sorting the list of distinct weeks in ascending order. The ASC argument specifies that the sort order should be ascending. If you want to sort the values in descending order, you can use the DESC argument instead.


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi v-henryk-mstf,

     

    Thank you for your reply with a detailed explaination. I tried your suggested approach and I ended up getting this error on the SORT function