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
Butterfly123
Frequent Visitor

List of product categories not bought by a selected customer but by similar customers

Hello there,

I have a table as data source that contains several elements such as customer names, customer categories, product categories, products and the related sales values (quantity, revenue, margin, etc.).

 

I need to create a suggestion function that works as follows:

- I select firstly a customer category from a slicer (i.e. restaurant, hotel, bakery, etc.)

- then I select a customer name from a slicer

- after that I would like to see in a table all the product categories bought by the customers in the same customer category except for that selected customer in the filter.

 

The purpose is to suggest products that other customer of the same category actually buy.

 

So let's say we have product categories A,B,C,D,E: if customer X of the customer category Y buys product category A,B but not D,E like his similars, I will se in the table D,E by selecting customer X in the slicer.

 

Hope it's enough clear!

Thanks in advance for your help.

Philip

5 REPLIES 5
TomMartens
Super User
Super User

Hey,

 

please create some sample data and upload the pbix file to onedrive or dropbox. This will make things much more simple.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey Tom,

 

was the file ok or do you need a better sample?

 

Regards

Philip

Hi @Butterfly123

set up the data model with a new table, with an inactive relationship with the original table

Then define measures as follows:

Using the second measure in a table gives the desired result:

Measure = MAX([product_code])
Measure 2 = CALCULATE (
    [Measure],
    CALCULATETABLE (
        SUMMARIZE (ALL(Sheet2),Sheet2[product_name],Sheet2[product_code],Sheet2[product_group],Sheet2[total_amount],Sheet2[total_costs],Sheet2[total_margin],Sheet2[total_qty]),
        ALL ( Sheet2[customer_name] ),
        USERELATIONSHIP ( Sheet2[customer_name], 'Table'[customer_name] )
    ),
    EXCEPT ( VALUES (Sheet2[customer_name] ), VALUES ( 'Table'[customer_name] ) )
)

5.png

 

See result on report2

 

Best Regards

Maggie

Hi Maggie,

 

thank you very much for your reply and file, really helpful!

My problem is that I display the products bought by the customers except the one I selected, but the products are maybe sometimes repeated. For instance in your screenshot flour 1kg is already bought by customer J and should not be displayed in the list, since it is not a suggestion to buy a new product. 

The list must not be divided by customer, the best would be a list of products bought by other customers of the same tag.

I.e. "other customers of same tag buy also... (product flour 25kg, etc..)"

 

Is something like that possible with DAX?

 

thank you in advance

Philip

Hey Tom,

 

thanks for your quick reply.

Here you find the link to the pbix file with a dummy datasource (excel file).

 

 

Regards

Philip

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.