link alternate part numbers

[aris]

Table A requested part number

partno	qty request
A	10

Table B alternate partno

partno	alternate partno
A	C

Table C ordered partno

orderno	partno	qty
PO1	A	5
PO2	B	3

2 visual tables:

1st shows partno and qty request from table A

2nd shows orderno partno and qty from table C

I want to make it so that when a partno is highlighed in visual 1, visual 2 shows both partno and alternate partno.

ex. partno A selected, will result in showing PO1 and PO2. if no line is selected then visual 2 is blank.

anyone can help me with this? many thanks

[aris]

i managed 🙂 many thanks again

[v-jingzhang · Accepted solution]

Hi @aris 

First add a custom step in Table B to transform it into the following format. 

= Table.Combine({#"previous step", Table.AddColumn(Table.SelectColumns(#"previous step", {"partno"}), "alternate partno", each [partno])})

Then create relationships:

Table A (partno) 1-->* Table B (partno)

Table B (alternate partno) 1-->* Table C (partno)

Create a measure as a flag. Apply it to the second table visual as a filter and set it to show items when value is 1. 

flag = IF(ISFILTERED('Table A'[partno]),1,0)

I have attached a sample file at bottom. Hope it helps. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[aris]

hi again 🙂

there seem to be some issue with relations... i cant get it to work. 😕

Would it be possible that you apply to attached and return?

https://drive.google.com/file/d/1KjKWkN2cP4UHifM2ijTH2i0RcoBd7Gz-/view?usp=sharing

Thank you very much in advance:)

Rgds

Aris

[aris]

thank you so much v-jingzhang

I am on holiday now, i will check it next week when back. it seems to be exactly what i need but will let you know 🙂 thanks again

PREVIEW

PREVIEW