Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hey,
please create some sample data and upload the pbix file to onedrive or dropbox. This will make things much more simple.
Regards
Tom
Hey Tom,
was the file ok or do you need a better sample?
Regards
Philip
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] ) ) )
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |