Creating a measure based off the relationship between two different columns and another table

[ajc96]

I've got my data structured as below in a contractProducts table, gives each combination of contract and product as well as the previous product for each contract (each contract can have more than one product).

contract_id	product_id	previous_product_id
1	1	1
2	2	1
2	3	3
3	3	2
4	1	3
5	3	1
6	2	3
6	1	2
7	3	3
8	1	1
9	1	2
9	2	3
10	3	1

I've also got a product benefits table (below), showing all the benefits selected for each product. 

product_id	benefit_name
1	legs
1	arms
1	head
1	back
2	legs
2	arms
2	hands
2	feet
3	legs
3	hands
3	eyes
3	left kidney

These have a many-many relationship between them based on the product id on each, I'd love to be able to refactor it or somehow split it all out to a much more sensible model, but due to the system I'm working with and how the benefits are structured and attached to product information that isn't feasible (I've tried but this is as close as it gets, long story short is that it's not really possible to make a unique list of benefits and/or products in a way that keeps the information correct).

What I want in power Bi is a matrix that shows each contract, which can expand to show all of their product benefits for their current product, and check whether each benefit is new (based on the previous product id) (picture below, as an image to make it easier to see how the matrix works, won't be entirely the same as the data above):

Essentially, I want a measure that finds:

For each contract

        For each product they have

                For each benefit in that product

                        Check if that benefit exists in their previous product and return yes/no

I've started with a measure using SELECTEDVALUE() to find the product id, previous product id, and the specific benefit name, I define each as a variable in the measure to reuse later. When I test each of these values in a matrix they all come out properly alongside the correct contract/product/benefits (i.e. return each SELECTEDVALUE() separately and display them in the matrix, values are correct).

That's where it starts to not work properly. I've tried a lot of options ( TREATAS, CONTAINS, FILTER, CALCULATETABLE etc.) but nothing seems to want to give me what I expect. I've managed to get partway there with GENERATE, generating a table from contract product and a filtered version of benefits to only the SELECTEDVALUE of the previous_product_id, if I COUNTROWS on this it gives me the right number for the previous product, but any time I try to then also apply a filter for the specific benefit into that generated table, or check if it contains the value it goes wrong (errors, blank tables, or a massive expansion where it shows every single benefit, with most of them blank and only the benefits from the previous product populated).

Thanks in advance for any suggestions.

[rajendraongole1]

Hi @ajc96 -Compare the current benefit to the previous product’s benefits using CONTAINS or a similar DAX function.

Create measure:

IsNewBenefit =
VAR currentProductID = SELECTEDVALUE(contractProducts[product_id])
VAR previousProductID = SELECTEDVALUE(contractProducts[previous_product_id])
VAR currentBenefit = SELECTEDVALUE(productBenefits[benefit_name])

-- Get the benefits of the previous product
VAR previousProductBenefits =
CALCULATETABLE(
VALUES(productBenefits[benefit_name]),
productBenefits[product_id] = previousProductID
)

-- Check if the current benefit exists in the previous product's benefits
VAR isNewBenefit =
IF(
NOT CONTAINS(previousProductBenefits, productBenefits[benefit_name], currentBenefit),
"Yes", -- Benefit is new
"No" -- Benefit exists in the previous product
)

RETURN isNewBenefit

Let me know if this works for your setup, if any please share more references and details.