Market "basket" analysis for non-order data

[Anonymous]

Hello,

I am a student new to Power BI and DAX commands.

I am attempting to do an affinity analysis on my data to see what subscriptions are most commonly subscribed to together. I have found a lot of basket analysis solutions here, but my data structure does not fit the typical product-->orderline<--order structure..

The data in my fact table consists of the following columns:

1. CustomerID

2. SubscriptionID

3. SubscriptionStartDate

4. SubscriptionCost

5. SubscriptionDiscount

I also have Subscription, Date, and Customer dimension tables.

I need to be able to create visualizations based on what subscriptions are most commonly subscribed to together. I'm just having trouble wrapping my head around the differences between my data and the typical "order" basket analysis examples.

Any help would be greatly appreciated!!

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.  Meanwhile, you may go through my solution - Perform an “Affinity analysis” to identify co-selling products.

Hope this helps.

[Anonymous]

Thank you for your example! I have revised a portion of it to show what I would like to acheive.

"Consider a dataset with four columns - SubStartDate, Customer_SK, Subscription_SK and Subscription Description.  To simplify, let's ignore columns 1 and 4 for now.  One may want to know the answer to the following question:

For a particular subscription (say subscription X), for how many customers (customer frequency) were subscription X and subscription Y subscribed to together OR for how many customers (customer frequency) were subscription X and subscription Z subscribed to together."

The issue is that I need to analyze this over time, but each row of my data has a different SubStartDate. For example, a customer subscribed to Hulu on 03/23/2016 and also subscribed to Netflix on 04/15/2016. This customer is subscribed to Hulu and Netflix but not until 04/15/2016. 

This link leads to my dataset in excel format. 

https://1drv.ms/x/s!AnbEveKwqdbIhjbGIS-ILqTem0L7

This link leads to my Power BI File

https://1drv.ms/u/s!AnbEveKwqdbIhjkkIapV0pG7QieG

[Ashish_Mathur]

Hi,

What problem do you face when you try to implement my solution (the link of which i have already shared with you)?

[v-frfei-msft]

Hi @Anonymous,

Please check the steps as below.

1. Create a calculated table.

Filter date = VALUES('Subscribers'[SubStartDate])

2. Create a calcualated column in the Subscribers table.

na = RELATED(Subscription[Name])

3. Create the measures as below.

Measure = var d = SELECTEDVALUE('Filter date'[SubStartDate]) 
return
IF(MAX('Subscribers'[SubStartDate])<=d,1,0)

Measure 2 = CALCULATE(DISTINCTCOUNT('Subscribers'[na]),FILTER(ALLSELECTED(Subscribers),[Measure]=1),VALUES(Subscribers[Customer_SK]))

Measure 3 = CALCULATE(DISTINCTCOUNT(Subscribers[Customer_SK]),FILTER(Subscribers,[Measure 2]=2))

Then we can get the result as below.

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank