Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sagadgreat
Helper I
Helper I

USERELATIONSHIP

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:

sagadgreat_0-1607019196865.png

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.  

3 REPLIES 3
Icey
Community Support
Community Support

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

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.  

Working on it. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors