Reply
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎01-18-2019
Accepted Solution

Selection of related items

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

 

 

Upit.png


Accepted Solutions
Moderator
Posts: 9,918
Registered: ‎03-10-2016

Re: Selection of related items

@mraka9,

Firstly, split column in Query editor as below.
Capture.PNG

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)

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Attachment

All Replies
Super User
Posts: 10,536
Registered: ‎07-11-2015

Re: Selection of related items

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 3
Registered: ‎01-18-2019

Re: Selection of related items

Thanks to Greg_Deckler, unfortunately I can not make the instructions Smiley Sad

Moderator
Posts: 9,918
Registered: ‎03-10-2016

Re: Selection of related items

@mraka9,

Firstly, split column in Query editor as below.
Capture.PNG

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)

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 3
Registered: ‎01-18-2019

Re: Selection of related items

Lydia you're genius

 

 

1.png2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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 Smiley Sad

 

but every honor. a genial idea

 

thank you