Capturing Selected occurrences with a many to many relationship

[N_R_000]

I'm going to need a few images to help explain this one:

Here's my dataset (I can't change the structure) of people who have trialed various products, then either purchased them (or not). 

Here are the relationships (Trials_Split is just the [Trials] table but the ProductsTrialed column has been split into rows based on the comma delimeter): 

I have a number of reporting requirements that I'm really struggling to achieve, and could do with the communitys help on: 

The main thing is linking a trial of a product to the purchae of that product but has to be AFTER the trial happened and within 30 days. 

1) Did the customer purchase the same product that they trialed within 30 days of the trial?

2) Did they purchase ANY product within 30 days of a trial? 

3) Which trial products were purchased most? A count for each. 

I was hoping to do this with a few flags as calculated columns, but due to the many-many relationship I can't find a way.

Really appreciate any help you can give.  

[Anonymous]

Hi @N_R_000 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous]

Hi @N_R_000 

I build three tables like yours and add calculated columns in these tables.

Trail Table:

Transform this table to Trail_Split and add a LatestTrailDate column in it.

LatestTrailDate = MAXX(FILTER(Trials_Split,Trials_Split[CustomerID]=EARLIER(Trials_Split[CustomerID])),Trials_Split[Date])

Sales Table add ProductID

ProductID = RELATED('Product'[ProductID])

Product Table:

Your Sales Table can't get product Id from Product Table by your relationship. You can build a relationship as below.

Then add a new calculated column in Sales table.

Flag = 
VAR _1 =
    CALCULATE (
        MAX (Trials_Split[LatestTrailDate] ),
        FILTER (
            Trials_Split,
            Trials_Split[CustomerID] = EARLIER ( Sales[CustomerID] )
                && Trials_Split[ProductID] = EARLIER ( Sales[ProductID] )
        )
    )
VAR _DayDiff =
    DATEDIFF ( _1, Sales[SaleDate], MONTH )
RETURN
    IF (
        _1 = BLANK (),
        "Not a Trail Product",
        IF (
            _DayDiff < 0,
            "Bought Before Trail",
            IF (
                _DayDiff <= 1,
                "Bought After Trail",
                "Bought But More than 1 month after Trail"
            )
        )
    )

Result:

For more info to relationships in power bi: Create and manage relationships in Power BI Desktop

You can download the pbix file from this link: Capturing Selected occurrences with a many to many relationship

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[N_R_000]

Hi @Anonymous 

Thank you, your help is really appreciated and has gotten me 90% of the way there. 

However, there is one issue I have: The data in the Product table actually creates a many to many relationship with the Sales table. 

This is because Trials are conducted under a 'Trial Product Name', and many of those trial products can link to just 1 sellable 'Product Name', like this: 

So that leaves me with a join like this - many to many 😞 

Is there a way I can achieve what you've already described, but with that relationship? 

I've tried changing the second clause in the filter to: 

&& Trials_Split[ProductID] = EARLIER ( Sales[ProductName] )

but every row get the 'Not a trial Product' result. 

[Anonymous]

Hi @N_R_000 

I change the Product table like yours, and build relationships between these tables.

Relationships:

I update the calculated column to add Product ID column into Sales Table.

ProductID = CALCULATE(MAX('Product'[ProductID]),FILTER('Product','Product'[ProductName]=EARLIER(Sales[ProductName])))

 And other steps are the same as above.

Result:

If this reply still couldn't help you solve your problem, please show me more details about your relationships.

And I just see Trial ProductName column in Product Table, other tables don't contain it. Will you use it in the future?

Or you can provide me with your pbix file by your OneDrive for Business.

You can download the pbix file from this link: Capturing Selected occurrences with a many to many relationship

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[N_R_000]

Hi @Anonymous  Thank again, deginitely closer! 

I got the data slightly wrong (sorry), which I need to explain more about: 

I've highlighted CustomerID 1 below; 

They trialed ProductIDs 22, 56 and 11. 

ProductID 22 links to 'ProductName' 'Bike22' but as you can see, 'Bike22' is linked to multiple trial bikes (ID 22 and 23). 

If I add your 'ProductID' Calculated Column to the Sales table, I get this result for CustomerID 1

It's not finding the link to the trial product because the MAX function returns 23; but I'd have the same problem if I switched it to MIN and the customer trialed ProductID 22.  

I have the same relationship view as you: 

And TrialSplit looks like this: 

In basic terms: "If the customer bought a bike which has a matching 'ProductName' to one he trialed, then it's a Match"

I can't share a OneDrive folder as it's a work problem, do you have an email address I could forward it to? 

[Anonymous · Accepted solution]

Hi @N_R_000 

To have a test I update my Trial Table, Sales Table(add customer 6 trial id 23 and bought Bike 22) and Product Table.

Trial Table:

Sales Table:

Product Table:

Relationships:

I think you can add a Product Name Column in Trial_Split Table. Update the Flag column and other steps are the same.

LatestTrailDate = MAXX(FILTER(Trials_Split,Trials_Split[CustomerID]=EARLIER(Trials_Split[CustomerID])),Trials_Split[Date])

Product Name = 
VAR ProductID = Trials_Split[ProductID]
RETURN 
CALCULATE(SELECTEDVALUE('Product'[ProductName]),ALL(Trials_Split),'Product'[ProductID]=ProductID)

Flag = 
VAR _1 =
    CALCULATE (
        MAX (Trials_Split[LatestTrailDate] ),
        FILTER (
            Trials_Split,
            Trials_Split[CustomerID] = EARLIER ( Sales[CustomerID] )
                && Trials_Split[Product Name] = EARLIER ( Sales[ProductName] )
        )
    )
VAR _DayDiff =
    DATEDIFF ( _1, Sales[SaleDate], MONTH )
RETURN
    IF (
        _1 = BLANK (),
        "Not a Trail Product",
        IF (
            _DayDiff < 0,
            "Bought Before Trail",
            IF (
                _DayDiff <= 1,
                "Bought After Trail",
                "Bought But More than 1 month after Trail"
            )
        )
    )

Result:

You can download the pbix file from this link: Capturing Selected occurrences with a many to many relationship

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@N_R_000 , You should have a common date and customer table remove join  M-M joins

https://www.seerinteractive.com/blog/join-many-many-power-bi/

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/