Forum Discussion
Advocate IICalculating product attach rate with USERELATIONSHIP
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!
4 Replies
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_bAnonymous Thanks for the input! Do you have any suggestion how to model this according to best practice?
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.
