Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Apologies if this question has been asked already, I didn't even know what terminology to use to search for what I want.
I have a table of contacts which is related to a leads table. There are several things I want to do.
In a table
1. Display only the contacts which are related to a lead with Subject A
2. List how many leads with Subject A have been related to the contact
3. List how many leads (Subject not important) is connected to the contact
If I use a page filter to say show me only contacts with Subject A, this affects point 3.
What would be the best strategy to isolate the contacts which meet this criteria without it affecting the measures I have created.
Edited to include data example
Contact Table
Contact GUID | Contact Name |
1234 | Person A |
1235 | Person B |
1236 | Person C |
Contact Table is connected one to many to the lead table via the GUID
Lead Table
Parent Contact Lead GUID | Contact Name | Topic |
1234 | Person A | Subject A |
1234 | Person A | Different Topic |
1236 | Person C | Subject A |
1235 | Person B | Topic 3 |
What I am seeing
Contact Name | Total Leads | Subject A leads |
Person A | 2 | 1 |
Person B | 1 | |
Person C | 1 | 1 |
Total | 4 | 2 |
What I see with a page filter on (filtered on contacts who have a lead with Subject A as the topic)
Contact Name | Total Leads | Subject A leads |
Person A | 1 | 1 |
Person C | 1 | 1 |
Total | 2 | 2 |
What I want to see
Contact Name | Total Leads | Subject A leads |
Person A | 2 | 1 |
Person C | 1 | |
Total | 2 | 1 |
Thanks
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Share some data, describe the question and show the expected result.
Hi Ashish, I added some data as per your request. My question is
What would be the best strategy to isolate the contacts which meet the criteria of "leads with topic = Subject A) without it affecting the measures I have created. As I mentioned in my original post, when I use a page filter it affects the display of leads which are not titled "Subject A"
Hi,
You may download my PBI file from here.
Hope this helps.
Great! I see where I went wrong...
In my calculations I had Total leads counting all leads and then I had another calculations for Subject A leads which isolated the leads with Subject A which ultimately affected the Total Lead count when I applied my filter. The formula you introduced for "Total leads" was a perfect solution. Thank you.
As an FYI for those with the same problem. The two calculations which worked for me were
Count leads = Countrows('leads')
which counts all leads based on the filter applied
Total Leads = CALCULATE([Count Leads],All(leads[subject]))
which counts all the leads regardless of the filter
You are welcome.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |