cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CVXLew
Regular Visitor

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

Accepted Solutions
camargos88 Community Champion
Community Champion

Re: How to use inative relationships in filter tables.

@CVXLew ,

 

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!
Ricardo

View solution in original post

4 REPLIES 4
camargos88 Community Champion
Community Champion

Re: How to use inative relationships in filter tables.

Hi @CVXLew,

 

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!
Ricardo

CVXLew
Regular Visitor

Re: How to use inative relationships in filter tables.

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

camargos88 Community Champion
Community Champion

Re: How to use inative relationships in filter tables.

@CVXLew ,

 

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!
Ricardo

View solution in original post

camargos88 Community Champion
Community Champion

Re: How to use inative relationships in filter tables.

Hi @CVXLew ,

 

Did it work ?

 

If yes, please mark it as a solution.

 

Thanks,

 

Ricardo


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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors