Selection of related items

[mraka9]

greeting,

apologies in my poor English language.

I need to make a filter that will link related items.

yellow, green and blue are labeled related items according to case, group and item.

Stock Number C0762202 is related to artifacts SL695, the article SL695 is related to PP782 and KHT25.

example:

I want to get all the related items when I choose the arctic C0762202

thanks in advance

[Anonymous · Accepted solution]

@mraka9,

Firstly, split column in Query editor as below.


Secondly, create the following columns in the table.

Column = CALCULATE( COUNTA(Table1[Stock Number]),ALLEXCEPT(Table1,Table1[Stock Number]))

Checkcolumn = CALCULATE(FIRSTNONBLANK(Table1[Stock Number],Table1[Stock Number]),FILTER(Table1,Table1[Case]=EARLIER(Table1[Case])&&Table1[Group]=EARLIER(Table1[Group])&&Table1[Item]=EARLIER(Table1[Item])&&Table1[Column]=2))

Thidly, create a new table using dax below.

Table = DISTINCT(Table1[Stock Number])

At last, create the following measures in original table, drag the chk measure to visual level filter and set its value to 1. You can review attached PBIX file to get more details.

mergedvalue = CALCULATE(CONCATENATEX(VALUES(Table1[Stock Number]),Table1[Stock Number],"/"),ALLEXCEPT(Table1,Table1[Checkcolumn]))

chk = var searchval=SELECTEDVALUE('Table'[Stock Number],"") return IF(ISERROR(SEARCH(searchval,[mergedvalue])),0,1)

Regards,
Lydia

[mraka9]

Lydia you're genius

I have a question

when i choose PP79, appears rttt15 (0007/2018-00, 99, 2) 

but when I choose rttt15, it just came out PP79 (0007/2018-00, 99, 2) and it does not appear PP79 (0050/2018-00, 1, 1)

the problem is that my base is very large and it takes a lot of time to get the result 😞

but every honor. a genial idea

thank you

[Greg_Deckler]

I believe you could create a measure like:

Measure = 
VAR __stockNumber = MAX('Table2'[Stock Number]) //gets currently filtered stock number
RETURN
IF(__stockNumber IN 'Table1'[Stock Number],1,0)

You could then create a table visual with Table1 columns and filter it by Measure = 1

[mraka9]

Thanks to Greg_Deckler, unfortunately I can not make the instructions 😞