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
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 🙂
Solved! Go to 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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |