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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Get a list of unique Accounts with not empty values of the other column

Hi All,

 

I have a table that includes columns from the Contact level and Account Level. The table contains a list of all employees from the Accounts that became our leads or clients this year.

 

On the Account level, we track the person who helped us generate a client (Lead Creator) and the client start date.

This data is the same for each Contact from the same Account. So, in fact, while I see Anna B and Brian from XXX Company and they have Lead Creator Berta, in fact it is only one Client, not two.

 

Also, I have records that are not clients yet.

 

Could you help me to create a measure that will calculate the number of Unique Clients based on this table?

1. I need to identify Unique Accounts based on the Account Name

2. I need to count rows if the Account is unique and Lead Creator field isn't empty

 

To sum up, I need to get a list of unique Accounts with not empty Lead Creator.

 

NameAccountLead Creator (Account)Client start date (Account)
Anna BXXX CompanyBerta4/5/2019
Brian XXX CompanyBerta4/5/2019
JemmaBortalameoWilliam3/5/2019
MarthaYYY  
DanielZZZ  
Brian YYYBerta5/5/2019
WesYYYBerta5/5/2019

 

 

 

 

 

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

What end result are you expecting?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Not 100% sure this is what you had in mind, but you can try:

Unique Accounts = DISTINCTCOUNT('Table4'[Account] )

Unique Accts, Lead Creater Blank = 
CALCULATE( COUNTROWS( Table4),
 Table4[Lead Creator (Account)] ="")

Unique Counts.png

Anonymous
Not applicable

@Anonymous , @Ashish_Mathur  thank you.

 

It is not exactly what I need. My goal is to calculate the number of clients/unique companies.

 

My challenges:

 

I have a list of contacts that do not always work for our clients 

There might be two or more people that work for the same client

 

I need to create a measure that:

 

  • will delete all not-client accounts
  • leaves only distinct accounts
  • calculates rows

Please let me know if it is still unclear

Anonymous
Not applicable

@Anonymous 

Any chance you can use the sample data from above and show what the goal output should be?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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