Forum Discussion

aris's avatar
aris
Frequent Visitor
3 years ago
Solved

link alternate part numbers

Table A requested part number

partnoqty request
A10

 

Table B alternate partno

partno alternate partno
AC

 

Table C ordered partno

ordernopartnoqty
PO1A5
PO2B3

 

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

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    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.