Calculating product attach rate with USERELATIONSHIP

[magnus_b]

Hi!

I'm having some trouble with calculating insurance attach rate with USERELATIONSHIP. I have created a sample PBIX file to illustrate the issue.

The goal is to calculate the attach rate of of each insurance product, by dividing the quantity of insurances sold by the quantity of compatible products sold. E.g. we have sold 4 laptops, and 2 laptop insurance, so the attach rate is 50%.

The simplified data model is shown below. If I have both relationships inactive, the attach rate is calculated correctly:

If I activate the "main" relationship, which is Categories.CategoryID -> Products.CategoryID, it seems that USERELATIONSHIP will no longer override the active relationship.

The problem with having both relationships inactive, is that I would have to use USERELATIONSHIP to specify the relationship in every other measure that use the default relation, which is Categories.CategoryID -> Products.CategoryID.

I have tried using TREATAS instead to create a virtual relationship, but this will return blank if there exists an active relationship between the tables, so that won't work either.

Any ideas on how to solve this? See attached PBIX file: https://drive.google.com/file/d/1GtIVFOQ3-wy7u4XqGLhT2bQyyVSYTccl/view?usp=sharing

I appreciate any help!

[Anonymous]

My advice? Change the model so that Best Practices are observed. Then you won't have problems. How can you have a 1-to-many relationship from Products to Categories and 1-to-many from Categories to Product at the same time? Makes no sense... And it does not matter that they are inactive. It just makes no sense from a modeling perspective.

[magnus_b]

@Anonymous Thanks for the input! Do you have any suggestion how to model this according to best practice?

[Anonymous]

I might tell you but I'd need to know the model and the data. Without such knowledge I don't think it would be possible. You can try to work on it on your own, of course, and get familiar with Best Practices. You should start here: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs and also read the articles to which links you'll see on the left-hand side. Dimensional Model is what you want to be very familiar with to do proper modeling in Power BI.

[magnus_b]

I am familiar with data modeling and normalization. I put the foreign key for insurance product in the category dimension because that is the granularity of the relationship: One category has exactly one or zero related insurance products.

I didn't intend or expect both of the relationships to be active at the same time. I used USERELATIONSHIP() to make sure only one relationship is active at the same time.

My sales data is contained in a single fact table, which includes both product sales and insurance sales. I believe this is good practice, and splitting it up would create a lot more complexity in my measures.

Did you look at the linked PBIX file? That contains the data model and sample data for this problem. Would appreciate your input!