Forum Discussion
Advocate IICalculating product attach rate with USERELATIONSHIP
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.
- 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!