Dependency problem between 2 tables + sort by column

[NathanPzi]

Hello,

To put you in context, I have 2 tables: Account and Contact.

In the "Account" table, I have :
- an "Account ID" column
- an "Account" column
- a "Major Account" column created by Power Query (so it doesn't have an ID), which lists large accounts.

Example:
Line 1: Account ID: 1 / Account : CGI New York / Major Account: CGI
Line 2: Account ID: 2 / Account : CGI Paris / Major Account: CGI
Line 3 : Account ID: 3 /Account : CAPGEMINI Washington / Major Account: CAPGEMINI

This table is linked to the Contact table by this account ID
With in the "Contact" table,
a "Contact ID" column
an "Account ID" column
Example:
Line 1: Contact ID: 124 / Account ID: 1
Line 1: Contact ID: 127 / Account ID: 2

The ultimate goal is to create a segment listing all major accounts, which is automatically sorted by Nb of contacts in that major account. ( In reverse alphabetical order )

The problem is that I'd like to be able to sort (using Power BI's "Sort by column") my major account by the number of contacts inside, not by the account name.
After several tests, either it doesn't work, or I come across a circular dependency, and I'm stuck. Unfortunately, ChatGPT doesn't help either :'(

If anyone can help me solve this problem. Thanks a lot 🙂

[v-junyant-msft · Accepted solution]

Hi @NathanPzi ,

I create a sample dataset by myself:

And I'm not sure what do you mean by "the number of contacts inside", I guess the phrase means the number of different contact IDs for each Major account?
So I use this DAX:

Contact_number = DISTINCTCOUNT(Contact[Contact ID])

Then I create a table visual:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.