Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:

  1. A product table which contains a set of barcodes and product names
  2. A basket table which effectively records a summary of a basket of products which were sold
  3. A basketitem table which has one row for each product which was in the basket (with a quantity)

The relationship looks like this:

 

ERD

 

 

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:

  1. Select a product name from the slicer on the report
  2. Have a  variable (if required) which will filter all of the baskets where that specific product was in
  3. 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 7
vik0810
Resolver V
Resolver V

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.

Anonymous
Not applicable

Thanks for the link - I am going to try the steps as per the link.

 

If I can't get it to work I will create a .PBIX file with a subset of data which I can share

 

Appreciate the assistance

v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I have managed to get a proper solution working. The simplified online DAX approaches for basket mix analysis are too simplistic, and don't work with complex relational datasets.

 

Thank you for the assistance though

Anonymous
Not applicable

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:

  1. A product table which contains a set of barcodes and product names
  2. A basket table which effectively records a summary of a basket of products which were sold
  3. A basketitem table which has one row for each product which was in the basket (with a quantity)

The relationship looks like this:

 Capture.PNG

  

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:

  1. Select a product name from the slicer on the report
  2. Have a  variable (if required) which will filter all of the baskets where that specific product was in
  3. 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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors