Circular reference and filtering

[zenisekd]

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... 

[v-rzhou-msft]

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. 

[zenisekd]

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. 

[v-rzhou-msft]

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. 

[zenisekd]

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. 

[v-rzhou-msft]

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.