List of customers not seen on a given topic (aligned to customer specialty

[Anonymous]

Hi all,  I have done some searching but not found a solution for the nuances I have - I would massively appreciate any guidance on this!.  I have created a worked example PBIX but not sure how to upload it

Target:  To create a table visual that shows my  relevant customers  (Table 1) who have not been seen on a given topic (Table 2) in a given time frame.  The extra nuance is that some topics are only relevant to certain customers

Example:

Table 1:  Customer List

Customer Name	Customer ID	Customer Expertise
Jim	1	General
Jim	1	Plumbing
John	2	Electrical
John	2	Plumbing
Alex	3	Electrical
Nick	4	General
Annie	5	Plumbing
Matt	6	General
Sam	7	General
Tammy	8	Plumbing
Henry	9	Electrical
Sandy	10	Plumbing
Ian	11	Plumbing
Milly	12	Electrical

Table 2:  Discussion Topics by Specialty

Target Expertise	Topic
Electrical	Earthing 101
Plumbing	Frozen pipe management
Electrical	General Safety
General	General Safety
Plumbing	General Safety

Table 3:  Interactions with customer

Date	Customer	ID	Topic
1/11/20	Sam	7	General Safety
1/12/20	Jim	1	General Safety
2/12/20	Jim	1	Other
8/11/20	Jim	1	Frozen pipe management
9/11/20	John	2	Frozen pipe management
20/11/20	Annie	5	Frozen pipe management

Desired output:

1. by selecting Table 2 "Frozen Pipe Management", Table 4 would show the list of Plumbing customers who had not been talked to about that topic (Tammy, Sandy and Ian) - would also like to combine this with a date range (so have they been seen on the topic in the given time window
2. If no topic is selected, table 4 would simply show all the customers not seen regardless of topic

I hope this makes sense - I would be enormously grateful if someone could help as the similar questions I have seen don't have the Table 2 topic piece (along with customer specialty)

Many thanks in advance,

Jim

[Ashish_Mathur]

Hi,

The answer should be Customer Names with ID's - 3,4,6,7,9 and 12.  Why have you listed the answers as Tammy, Sandy and Ian only?

[Anonymous]

Hi there -  its because the "Frozen pipe mangement " is only relevant for plumbing....

• Jim, John, Annie, Tammy, Sandy and Ian are the customers aligned to plumbing
• Jim, John and Annie have been seen on the topic in Table 3, which leaves Tammy, Sandy and Ian as the plumbing customers who have not been seen on Frozen pipe management.

Hope this makes sense:)

Cheers,

JB

[CNENFRNL · Accepted solution]

@Anonymous , as all of your tables are fact tables, it's necessary to create more lookup tables to establish 1:* relationships among them. I rearrange them this way,

Filter propogation in such a data model isn't that easy. You might want to refer to the attached file for details.

[Anonymous]

Wow - that works perfectly - never used Treatas before!  Now to try and bring it to life in my crazily complicated model.  

Thankyou so much - you are a rock star!

[Anonymous]

BTW - I know my schema needs tidying- this started as a hobby and has grown arms, legs and all sorts else!  Will be looking for some professional help (of all kinds:)

[lbendlin]

Have a look at the cross filter/cross apply concept. Basically you create a cartesian product of your tables and then work with the "holes" ie the intersections without data.

[Anonymous]

Thanks - we use cross filters alot but have not been able to use it with topics specific to certain customer populations and select time window...  I am sure it is possible but it is beyond me:)