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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to use inative relationships in filter tables.

 

Hello,

 

I have the following data model where the component Item ID has an inactive relationship with the Item table.

Based on my selection of Primary Item ID, I'm able to pull Sales Transaction data via the active relationship.

Based on that same selection of Primary Item ID, I would like to also pull purchase data for the component Item ID using the inactive relationship.  How could I accomplish the latter whilst maintaining the active relationship to pull sales data for Primary Item? 

additional information/clarification:

 

I've uploaded a dumbed down version of my pbix to highlight my requirements.  Basically, I need want to pass all 'DimBom'[CItemId] selected, filtered by the selection of 'DimBom'[PItemId], to the FactPurchase Table to pull the related qty purchases.  there is an inactive relationship between 'DimBom'[CItemId] to 'DimItem'[ItemId]

I also tried to create a measure to pull the quantity, but I'm sure I mucked it up because I'm not familiar with its function.

buys = CALCULATE(sum(FactPurchase[Qty])|TREATAS(VALUES(DimBom[CItemId])|DimItem[DimItemId]))

 

Sample Pbix included:

https://drive.google.com/open?id=1bzIeuXC-eez7TwGq8NzzbVl855yi_S8l

 

Data Model and sample 

 

Annotation 2020-04-06 172751.png

 

 

 

Primary Item IDComponent Item ID  
1000110002  
1000110003  
1000110004  
    
Sales TransactionPrimary Item IDQty 
1000001000110 
    
PurchasesPrimary Item IdComponent Item IDQty
20001100011000210
20001100011000320
20001100011000430
 
1 ACCEPTED SOLUTION

@Anonymous ,

 

Try this measure:

 

buys = CALCULATE(SUM(FactPurchase[Qty]); USERELATIONSHIP(DimBom[CItemId]; DimItem[DimItemId]))
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi there, I've uploaded a dumbed down version of my pbix to highlight my requirements.  Basically, I need want to pass all 'DimBom'[CItemId] selected, filtered by the selection of 'DimBom'[PItemId], to the FactPurchase Table to pull the related qty purchases.  there is an inactive relationship between 'DimBom'[CItemId] to 'DimItem'[ItemId]

 

I also tried to create a measure to pull the quantity, but I'm sure I mucked it up because I'm not familiar with its function.

buys = CALCULATE(sum(FactPurchase[Qty])|TREATAS(VALUES(DimBom[CItemId])|DimItem[DimItemId]))

 

https://drive.google.com/open?id=1bzIeuXC-eez7TwGq8NzzbVl855yi_S8l

 

Regards,

William Lew

@Anonymous ,

 

Try this measure:

 

buys = CALCULATE(SUM(FactPurchase[Qty]); USERELATIONSHIP(DimBom[CItemId]; DimItem[DimItemId]))
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Hi @Anonymous ,

 

Did it work ?

 

If yes, please mark it as a solution.

 

Thanks,

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



camargos88
Community Champion
Community Champion

Hi @Anonymous,

 

Check these links: 

 

https://docs.microsoft.com/pt-br/dax/userelationship-function-dax

 

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.