Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Primary Item ID | Component Item ID | ||
10001 | 10002 | ||
10001 | 10003 | ||
10001 | 10004 | ||
Sales Transaction | Primary Item ID | Qty | |
100000 | 10001 | 10 | |
Purchases | Primary Item Id | Component Item ID | Qty |
20001 | 10001 | 10002 | 10 |
20001 | 10001 | 10003 | 20 |
20001 | 10001 | 10004 | 30 |
Solved! Go to Solution.
@Anonymous ,
Try this measure:
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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |