cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 columns, 'Grade' (text) and 'Order' (whole number).  

Grade   Order

Teachers1
 2
P43
P54
K5
16
27
38
49
510
611
712
813
914
1015
1116
1217

My DAX Measure statement is:

List of Grade Values: =
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"
        )
    As you can see, the CONCAT statement is set to order by  'Grade_Sort_Order'[Grade], whereas I need it to sort by  'Grade_Sort_Order'[Order] but the statement does not recognize the 'Order' column in the table. If I change it from Grade to Order I get the following error: 
"A single value for column 'Order' in table 'Grade_Sort_Order' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
So using the order by of Grade, the order results of a selection of P4, 1, 4, 10 is: 1, 10, 4, P4 when what I need is P4, 1, 4, 10.
I have tried changing the column format to text and using 01, 02, 03, ect. but this did not work.
I have probably over explained this but I feel it is best to be wordy rather than vague.
Any thoughts are much appreciated.
Dan
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: DAX Measure Sort by issue

@Dan01 - 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"
        )

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: DAX Measure Sort by issue

@Dan01 - 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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: DAX Measure Sort by issue

@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

Highlighted
Super User IV
Super User IV

Re: DAX Measure Sort by issue

@Dan01 - 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"
        )

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: DAX Measure Sort by issue

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors