Forum Discussion
Circular reference and filtering
Hi everyone,
Recently I run into a puzzle.
Our customer sells products, that can be sold as a single product or as an assembly of products (set).
When, upon request, I created ABC analysis, based on which he could sell out or discount C products. It turns out that he cannot simply get rid of C products because occasionally these products are included in A or B set of products. Here I need to mention that the ABC analysis is dynamic in time (it recalculated ABC based on the date range in slicer).
I have TABLE 1, which lists products, sales, and ABC class. Now I wanted to create TABLE 2 which would be filtered if I clicked in TABLE 1. It would show a list of products (sets), sales, and QTY, which include selected products from TABLE 1. The important part is, that the sales in TABLE 2 and 1 are filtered by Date slicer. The picture below will hopefully explain better.
I tried different ways of making unactive relationships and calculating with USERELATIONSHIP, but it didn't work...
5 Replies
Hi zenisekd
I think you want to click XX in ABC table , filter BOM Table by XX to get BOM ID =1, then filter BOM Line table by BOM ID TO get two rows in yellow in your screenshot.
However I am confused about how to get YX value by XX in ABC Table, I couldn't find YX in BOM line.
Could you tell me more about your calculate logic?
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.
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.
