Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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,
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
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
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
Thanks to Greg_Deckler, unfortunately I can not make the instructions 😞