Forum Discussion
How To Use Slicer As Input Into A Table Display To Find Similar Items
Good Day
I have a very specific use case that I am trying to solve without any success so far.
The background:
I am working on a set of (basket) data which contains three tables. The tables include the following:
- A product table which contains a set of barcodes and product names
- A basket table which effectively records a summary of a basket of products which were sold
- A basketitem table which has one row for each product which was in the basket (with a quantity)
The relationship looks like this:
The scenario
I am currently using PowerBI Desktop. I have a report which has a slicer on which enables me to select a specific product name. A user can select a product name, which will then show (in a table) all of the meta information about the product - i.e. basketIDs, date of sale, quantity sold.
What I would like to do is the following:
- Select a product name from the slicer on the report
- Have a variable (if required) which will filter all of the baskets where that specific product was in
- Then take the list of basketIDs from step 2, and display all items which were also in the same baskets given the filtered set of basketIDs
I basically want to find out, given a product X, what other products are being sold in the same basket.
The end goal would be to display the details in a table
7 Replies
Good Day
I have a very specific use case that I am trying to solve without any success so far.
The background:
I am working on a set of (basket) data which contains three tables. The tables include the following:
- A product table which contains a set of barcodes and product names
- A basket table which effectively records a summary of a basket of products which were sold
- A basketitem table which has one row for each product which was in the basket (with a quantity)
The relationship looks like this:
The scenario
I am currently using PowerBI Desktop. I have a report which has a slicer on which enables me to select a specific product name. A user can select a product name, which will then show (in a table) all of the meta information about the product - i.e. basketIDs, date of sale, quantity sold.
What I would like to do is the following:
- Select a product name from the slicer on the report
- Have a variable (if required) which will filter all of the baskets where that specific product was in
- Then take the list of basketIDs from step 2, and display all items which were also in the same baskets given the filtered set of basketIDs
I basically want to find out, given a product X, what other products are being sold in the same basket.
The end goal would be to display the details in a table
Hi Anonymous,
I already have the idea about this scenario now. Please share a dummy pbix file. It's hard to write a DAX formula without data.
Some tips,
1. You could use CALCULATE, VAR and IN in your formula.
2. You can check the items if they are in the selected basket, then filter the negative value out. IF( value in {}, 1, 0), then filter out 0.
Best Regards!
Dale
Any chance you might be able ot provide me with an example of how I could go about using it?
I have been following some channels to get permission to create a sub-set of the sensitive data so I can share a .pbix file, but I have not managed to get the authorisation yet.
I also had a look at the link provided for the basket analysis. This works well if you are looking for summarised values/calculations, but not when the idea is to:
- Select a barcode from a slicer; and
- Display a table with all the baskets which had the product in, whilst display the other products which were also in the basket
Should I perhaps set up a fake XLS document with a couple of simulated baskets etc and to then share that?
Hi Anonymous,
You can share the pbix file directly. Upload it to a cloud drive like GoogleDrive, Dropbox, then share the link here. Please mask the confidential parts.
Best Regards,
Dale
Hi, there is a website www.daxpatterns.com. You will find there a pattern regarding your problem http://www.daxpatterns.com/basket-analysis/. The site owners, Marco Russo and Alberto Ferrari are DAX gurus and write also very good books.
