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
junglejimsvet
Helper I
Helper I

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

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 NameCustomer IDCustomer Expertise
Jim

1

General
Jim1Plumbing
John2Electrical

John

2Plumbing
Alex3Electrical
Nick4General
Annie5Plumbing
Matt6General
Sam7General
Tammy8Plumbing
Henry9Electrical
Sandy10Plumbing
Ian11Plumbing
Milly12Electrical

 

Table 2:  Discussion Topics by Specialty

Target ExpertiseTopic
ElectricalEarthing 101
PlumbingFrozen pipe management
ElectricalGeneral Safety
GeneralGeneral Safety
PlumbingGeneral Safety

 

Table 3:  Interactions with customer

DateCustomerIDTopic
1/11/20Sam7General Safety
1/12/20Jim1General Safety
2/12/20Jim1Other
8/11/20Jim1Frozen pipe management
9/11/20John2Frozen pipe management
20/11/20Annie5Frozen 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

 

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

@junglejimsvet , 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,

Screenshot 2021-01-05 225938.png

 

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

Screenshot 2021-01-05 230016.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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?


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

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
Community Champion
Community Champion

@junglejimsvet , 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,

Screenshot 2021-01-05 225938.png

 

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

Screenshot 2021-01-05 230016.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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!

 

 

Picture1.jpg

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
Super User
Super User

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.

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:)

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.