cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mraka9 Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

4 REPLIES 4
Super User IV
Super User IV

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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

mraka9 Frequent Visitor
Frequent Visitor

Re: Selection of related items

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

Moderator v-yuezhe-msft
Moderator

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

mraka9 Frequent Visitor
Frequent Visitor

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 😞

 

but every honor. a genial idea

 

thank you

 

 

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors