Sort one column by another - messy data

[JimJim]

Hi Guys,

I have a report: products.pbix and am trying to sort output by output_description output_description by output so that the sort order on my slicer is more user friendly, but the problem is the data is quite messy and I can't create the sort. Is there an alternative?

[JimJim]

Hi @Ritaf1983 , apologies for not being clear. When I attempt to sort column output_description by output, I get an error message:

"We can't sort the output_description column by output. There can't be more than one value in output for the same value in output_description"

[Ritaf1983]

Hi @JimJim 

Technically, you've explained yourself perfectly. The error message is clear and well-known.

However, to better assist you, we need to understand the logic behind what you're trying to achieve.

Based on the data in the table, as I explained in my previous response, there's no reason to sort these columns one by another since the order is already identical.

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

[JimJim]

@Ritaf1983 , I would like the order of values in my slicer to sort by the output (numerical value) and not by the output_description (text value). Here is an image of my current problem, as you can see, 13 comes after 1290

[Ritaf1983 · Accepted solution]

@JimJim 

This is the opposite of what you asked in the original question 🙂

And it's solvable. Here are the steps you need to take:

1. From PQ duplicate table :

2. Remove all the columns except 2 needed :

3. Remove duplicates from output description:

4. Filter out "null" (if you have)

5. Close and apply

6. create a relationship between the sort table and the main table

7. In the sorting table apply a wanted sorting logic :

8. Use the sorting table as a slicer, it will filter the main table due to their relationship :

The pbix is attached

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

[JimJim]

@Ritaf1983 , thank you so much, I did indeed mess up my original post and have edited it to prevent others from being confused. I really appreciate your help.

[Ritaf1983]

 I'm always happy to help, and of course, my comments are not meant to be condescending or prove you wrong. Rather, I'm trying to clarify what is needed so I can better assist you 🙂

[Ritaf1983]

Hi @JimJim 

Based on the information in your file, the order of output and output_description is identical since one is derived from the other.

The output_description appears to be a simple concatenation of output with units_measure.

Therefore, it's difficult to understand your intended meaning. Could you please provide an example of the desired outcome?

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