Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Measure Sort by issue

I have a card on a report page that displays selected choices from slicers and I am having an issue obtaining the correct sort order for one of them. The measure is based on a simple table of two col...
measure
measure based on slicer
sorting
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Here is where you are limiting your table. I would replace what is in red bold below with this:

     

    'Grade_Sort_Order'

     

    So, just the table, which would include all columns in the table.

     

    VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Grade_Sort_Order'[Grade])
    VAR __MAX_VALUES_TO_SHOW = 17
    RETURN
        IF(
            __DISTINCT_VALUES_COUNT <> __MAX_VALUES_TO_SHOW,
            CONCATENATE(
                CONCATENATEX(
                    TOPN(
                        __MAX_VALUES_TO_SHOW,
                        VALUES('Grade_Sort_Order'[Grade]),
                        'Grade_Sort_Order'[Grade],
                        ASC
                    ),
                    'Grade_Sort_Order'[Grade],
                    ", ",
                    'Grade_Sort_Order'[Grade],
                    ASC
                ),
                ""
            ),
                "All"
            )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Greg_Deckler  - Hi Greg, and thank you for the responce.

Sorry for the delay in getting back with you. Right after I made my post I got slammed with work and couldn't get back until now. 

I have tried manay different ways to "feed CONCATENATEX" with the (Grade_Sort_Order.[Order] column but nothing I try has worked.

Where, in my expression above, would I reference the [Order] column?

Thank you for your help.

Dan

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Anonymous - Here is where you are limiting your table. I would replace what is in red bold below with this:

 

'Grade_Sort_Order'

 

So, just the table, which would include all columns in the table.

 

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Grade_Sort_Order'[Grade])
VAR __MAX_VALUES_TO_SHOW = 17
RETURN
    IF(
        __DISTINCT_VALUES_COUNT <> __MAX_VALUES_TO_SHOW,
        CONCATENATE(
            CONCATENATEX(
                TOPN(
                    __MAX_VALUES_TO_SHOW,
                    VALUES('Grade_Sort_Order'[Grade]),
                    'Grade_Sort_Order'[Grade],
                    ASC
                ),
                'Grade_Sort_Order'[Grade],
                ", ",
                'Grade_Sort_Order'[Grade],
                ASC
            ),
            ""
        ),
            "All"
        )
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you Greg. That did the trick. I never thought of referring to the table as a whole, I was concentrating on adding a reference to the [Order] column itself.

    I appreciate your time.

    Dan