USERELATIONSHIP

[sagadgreat]

I have combed through several posts and cannot seem to find the answer I need.  First of all, based on everything I see, my request should be simple, but I wonder if the type of relationship is what matters. My issue: 

I have total sales based on parts for two different years.  The basic formula is Total2019 = sum(Shipments2019[Extended_Amounts]).  To simplify the breakdown for the shipment file is a customer number/name -> order number -> Item_Number -> Qty -> Price -> Qty * Price = Extended_Amounts setup.  

The parts themselves however have alternates that are related to them (part ABC is related to part ABC1) and customers will purchase either or based on their preference of part (one is a premium part for instance).  I need to be able to see how many sales dollars we have on the alternate part to see if we should continue to sell the main part# to them.  

I have created a parts selection file that will match the alternate item to it's main part# (parts list and BD Parts Alt in the below:

An active one to many relationship is created between parts list and Shipments2019[Item_Number], an incative one between BD Parts Alt and Shipments2019[Item_Number].  The inactive one however is a many to many as some alternates will apply to multiple main part#'s. The alternates are also a subset of the main parts list as well.   

The userelation function is this: 

BD Alt Total Spend 2019 = CALCULATE(SUM('Shipments2019'[Extended_Amounts]), USERELATIONSHIP('Parts List'[BD Parts Alts], 'Shipments2019'[Item_Number]))

In the result, we sold many more of the alternate in 2019, but both totals are the same.  

(won't let me paste the pic but ex: Parts List = 64321 BD Parts Alt = 64575 BD Alt Total Spend 2019 = $143 Total2019 = $143

Is it because of the many-many relationship perhaps?  Any assistance is greatly appreciated.  Let me know if I need to share more info.  

[Icey]

Hi @sagadgreat ,

Can you show me some sample data or ceate a dummy .pbix file for test? Please don't contain real data and sensitive information.

Best regards

Icey

[sagadgreat]

So in creating this test sample, I noticed something in the data pull with the way I had it setup previously and made a basic fix.  I'm sending two versions.  

Version 1: based on the original problem statement.  

Note: (see below on version 2, if I attempted to resolve using this method, creating a separate relationship between the BD Parts file and sales files, I get a circular dependency)

https://1drv.ms/u/s!ArmEo9mldkux9EBLdm4xJdCl9IW-?e=dPj7kW 

Version 2: I removed the BD parts from the parts list and created relationships from the BD parts directly to sales.  I then was able to use the USERELATIONSHIP function properly.  The issue now is I cannot figure out how to display the alt parts for the original parts list on the same table without the duplication.  The end user needs to see both representations to know that the original item has an alt so they can click and filter accordingly (as displayed in version 1).  

https://1drv.ms/u/s!ArmEo9mldkux9D-FA-pMgEgOeyNJ?e=Haol48 

Thanks for any assistance you can provide.  I'm still working the issue on this side as well.  

[sagadgreat]

Working on it.