Cannot sort Name by Id because at least one value in Name has multiple distinct values in Id.

Question

I am working in SSAS... I have column Name, but I want to sort by Id because when in Power BI, it automatically groups by people with same name, but they are in fact different people...

I have 2 tables: Clients and Current Client.

Client is a table and Current Client is a calculated table…

I am able to do it in Client, but not in the calculated table Current Client, Why is this? I get the error….

Cannot sort Name by Id because at least one value in Name has multiple distinct values in Id. For example, you can sort [City] by [Region] because there is only one region for each city, but you cannot sort [Region] by [City] because there are multiple cities for each region…

Well, yes, that’s the very reason I want to sort!!

Answer 1

If you have multiple customers with the same name add a sort by column on id will not split these rows anyway.

In SQL it would do the equivalent of:

SELECT DISTINCT CustomerName from Custom ORDER BY CustomerId

So without this error if you have 2 "John Smith" customers they would still appear as 1 customer.

Your only solution in tabular models is to add something to each customer name to make them unique.

You could either do something like adding the customer id. eg.

"John Smith (123)"
"John Smith (456)"

Or if you wanted to complicate things you could look at adding non-breaking spaces after some of your duplicates

"John Smith"
"John Smith "

Note that normal whitespace will be stripped off during processing, you would specifically have to inject an non-breaking space character somehow and figure out some logic to maintain the distinct versions of your "John Smith" entries.

Answer 2

Hi @maca128-1653,

Welcome to Microsoft Q&A!

And you could refer to this similar thread, see below:

Because Past is linked with multiple values, I created a new calculated column where when it is past the value is 1, else the value is YearMonth. & then sort order on your new calculated column

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Well, yes, that’s the very reason I want to sort!!

Actually what you want is to distinguish between individual clients-namesakes, not to sort their names. When the time comes to apply sorting to the list of names, the names have already been made unique.
Now the distinguishing would be trivial in MD by using Id as the key of the attribute. Alas, Tabular is more limited here.
Besides the solutions suggested by Darren, you could use a workaround, putting the Id column into any client-level visual alongside the Name column.