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"
            )
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Anonymous - If I am understanding your code correctly, it looks like you are only feeding CONCATENATEX a single column "Grade" and thus your Order column is not available. You will need to start with both of your columns being fed into CONCANTENATEX and then you should not have a problem.

  • 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