How To Use Slicer As Input Into A Table Display To Find Similar Items

[Anonymous]

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:

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

[vik0810]

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]

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]

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

[Anonymous]

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?

[v-jiascu-msft]

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

[Anonymous]

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]

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:

 

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