Filter table based on relative date and specific column values from same table

[Anonymous]

Hi all,

I am relatively new to PowerBI and I need some help with filtering on relative dates and column values frome the same table.

The problem is as follows:

In PowerBI we make a live connection with a tabular model from SSAS. 

We have several tables that are all linked to one customer table with a customerkey,  see simplified version below: 

All the tables (questionnaire/supermarket/appointments) include a row with datetime.

What I would like to have is a selection of customers that ordered a specific Product (product A) , and after a spefic time period also ordered another product (product B). I can't figure out how to make this work in PowerBI and DAX. 

Ideally, the end product looks something like this:

Where you could choose product A (pancakes) with a range of dates and it shows the amount of customers who bought product A (130 customers) and then you choose product B (milk) and a time interval (28 days) and it shows all customers that within 28 days of buying product A Also bought product B (125 customers). 

We have tought of duplicating the entire product table, which makes it easier to get this selection of customers, but as we would like to have this filter options for all of our tables (products, appointments etc.) and also the combination between tables (customers who bought product A and after x amount of days had appointment B) we hope there is an option of doing this without duplicating all these tables. 

Hopefully someone knows how to fix this!

[Anonymous]

Thank you for your answer, however I was hoping for a more dynamic solution. This solution only works for cross selling analysis, but we would also like to to have these filter options for all of our tables (products, appointments etc.) and also the combination between tables (customers who bought product A and after x amount of days had appointment B). Therefore we hope there is an option of doing this without duplicating all the tables. 

[DataInsights]

@Anonymous,

You should be able to achieve this with virtual tables in DAX measures. The concept is to create a virtual table that meets the conditions, and then further filter it for the additional conditions. The intersection of the two tables would be a table expression that can be used in a CALCULATE measure. You'll need a clone of the Product table (no relationships) for the second Product slicer. The TREATAS function would be an option for applying the Product filter from the cloned Product table to the main Product table.

Are you able to share a sanitized pbix via one of the file services like OneDrive?

[DataInsights]

@Anonymous,

See the article below that shows how to evaluate whether a particular product is bought with another product:

https://blog.enterprisedna.co/advanced-basket-analysis-example-in-power-bi-cross-selling/