Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Name | Account | Lead Creator (Account) | Client start date (Account) |
Anna B | XXX Company | Berta | 4/5/2019 |
Brian | XXX Company | Berta | 4/5/2019 |
Jemma | Bortalameo | William | 3/5/2019 |
Martha | YYY | ||
Daniel | ZZZ | ||
Brian | YYY | Berta | 5/5/2019 |
Wes | YYY | Berta | 5/5/2019 |
Hi,
What end result are you expecting?
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)] ="")
@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:
Please let me know if it is still unclear
@Anonymous
Any chance you can use the sample data from above and show what the goal output should be?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |