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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
NathanPzi
Frequent Visitor

Dependency problem between 2 tables + sort by column

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 🙂

1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

Hi @NathanPzi ,

I create a sample dataset by myself:

vjunyantmsft_0-1702519698819.pngvjunyantmsft_1-1702519743670.png

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:

vjunyantmsft_2-1702519980176.pngvjunyantmsft_3-1702520024198.pngvjunyantmsft_4-1702520038904.png


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.

 

View solution in original post

1 REPLY 1
v-junyant-msft
Community Support
Community Support

Hi @NathanPzi ,

I create a sample dataset by myself:

vjunyantmsft_0-1702519698819.pngvjunyantmsft_1-1702519743670.png

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:

vjunyantmsft_2-1702519980176.pngvjunyantmsft_3-1702520024198.pngvjunyantmsft_4-1702520038904.png


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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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