If a value exists in two columns, mark with "Yes"

[JohmRA]

Hello, 

I have the table below

I am using slicer based on "Main Item Number" 

what I am trying to achieve, when I filter for example by "11", the following tables to be shown. 

not only "QQQ" but also the "QQC" and "QQD" as they are part of "QQQ" 

also to add a new col, if the item number exists in "Assembly item" to be marked with "Yes"

Thanks in advance

[Anonymous · Accepted solution]

Hi @JohmRA 

For your question, here is the method I provided:

Here's some dummy data

“Table”

You need to create a new disconnected table to act as a slicer.

Table 2 = SUMMARIZE('Table','Table'[Main item number])

“Table 2”

Create a new column.

Mark =

VAR _select_rownumber = 'Table'[Row number]

VAR _current_assembly_item = 'Table'[Assembly item]

VAR _next_assembly_item = CALCULATE(SELECTEDVALUE('Table'[Assembly item]),FILTER(ALL('Table'),'Table'[Row number] = _select_rownumber +1))

RETURN

IF(

   _current_assembly_item > 0 || _next_assembly_item > 0,

   "Yes",

   BLANK()

)

Create a  measure.

Measure =

VAR _select_number = SELECTEDVALUE('Table 2'[Main item number])

RETURN

IF(  

   ISFILTERED('Table 2'[Main item number]),

       IF(

           SELECTEDVALUE('Table'[Main item number])=_select_number || SELECTEDVALUE('Table'[Assembly item]) = _select_number,

           SELECTEDVALUE('Table'[Main item name]),

           BLANK()

       ),

   SELECTEDVALUE('Table'[Main item name])

)

Here is the result.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Selva-Salimi]

@JohmRA 

One of the easiest way is to create a calculated column as follows and then use this column as your slicer:

calculatedColumn:= if ([assembly item]=0, [main item number] , [assembly item] )

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.