Forum Discussion
Circular reference and filtering
Firstly, my big apologies, I made a mistake in the table BOM line, naming the line YZ instead of YX. I just corrected the picture.
Nevertheless, I want to click on XY in ABC table and get info that XY is inside of products XX and XY and their sale (of XY and XX) and QTY is .... This mechanism is meant to prevent somebody to sell out C item, even though they are also part of a set item, which is an A.
Hi zenisekd
Try to build an unrelated table by Product ID column, then build a filter measure.
My Sample:
ABC Table:
BOM Table:
BOM Line Table:
Slicer Table:
Slicer = VALUES('ABC table'[Product ID])Relationship:
Measure:
Measure =
VAR _Sel = SELECTEDVALUE(Slicer[Product ID])
VAR _BOMID = CALCULATETABLE(VALUES('BOM lines'[BOM ID]),FILTER(ALL('BOM lines'),'BOM lines'[Product ID]=_Sel))
Return
IF(MAX(BOM[BOM ID]) in _BOMID,1,0)Build a table visual as below, add measure into filter field and set it show items when value =1.
QTY is count of BOM Line ID in BOM line table.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Rico,
I am sorry, but this is not the solution I was looking for. I need to have both tables on the page, so that primarly I could scroll through ABC table and see all the details about the products and if I wanted to, I could click on specific line of the table, which would filter TABLE 2 and gave me info about a) in which sets is the product used and b) what was the sale of these sets.
With your solution, I have a slicer, which however doesn't meet the requirements since I need to see the details of TABLE 1.Hi zenisekd
Could you share a sample with me by your Onedrive for Business?
This will make it easier for me to understand your requirement.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.