Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Merleau
Helper II
Helper II

Sorting multiple columns containing list of strings in DAX

Hello,

I need help to sort some columns in my report. The columns contain a list of strings.

For example, my original table looks like the following:

 

ID

Type1

Type2

Type3

Name

ID1

English, Italian, Yen, French

Spanish

Greek, Armenian

ID1-name

ID2

French, English

Yen, Korean

 

ID2-name

ID3

 

Italian, Arabic

 

ID3-name

 

After sorting the columns Type1, Type2 and Type3 in alphabetical order, I would like my table to look like this:

 

ID

Type1

Type2

Type3

Name

ID1

English, French, Italian, Yen

Spanish

Armenian, Greek

ID1-name

ID2

English, French

Korean, Yen

 

ID2-name

ID3

 

Arabic, Italian

 

ID3-name

 

Can somebody pls help?

Thank you

5 REPLIES 5
mahoneypat
Employee
Employee

Not sure what analysis you have planned, but you might consider splitting the languages and unpivot.  In any case, you can add a custom column to get your sorted values.  It is easier to do this in the query editor, with a formula like this in the new column.  It will create a list from your text, sort it, then combine the values back again.

 

= Text.Combine(List.Sort(Text.Split([Column1], ", ")), ", ")

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thanks @mahoneypat 

 

Actually, those columns are dynamically created in Power Query and in my real data, there are not languages. Also, those lists are much longer. The goal was to create and sort them dynamically in M, if possible. But that was really complicated. I have a related post where this was discussed in the Power Query section.

 

Otherwise, is it possible to just sort them in the report in DAX?

Thanks.

This really is better done in M/query.  Did the suggested M code not meet your needs?  If the columns are generated in the query editor, can you add List.Sort into the mix to have them sorted when first created (to save processing time)?

 

Your other option is to split the values and unpivot (also in the query editor).  Once you do that, you can use a CONCATENATEX() expression on the DAX side to put them back in sorted order.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you again at @mahoneypat 

Actually, this problem was already solved in M as described here .

I wanted to know if there was a better and cleaner solution in DAX.

Thanks again.

 

Thanks for the explanation.  This could be done in DAX with the approach found in this post

https://community.powerbi.com/t5/Desktop/DAX-how-split-a-string-by-delimiter-into-a-list-or-array/td...

 

In the same expression you could re-concatenate things with CONCATENATEX using the order by expression or ASC or DESC.

 

But I still recommend M for this type of transformation.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors