Forum Discussion

micklowe's avatar
micklowe
Icon for Helper I rankHelper I
3 years ago
Solved

Building a new table from two BI tables

Hi Everyone,

 

I'm trying to build a table based on 2 existing tables but I need to join to one of the tables using a parameter, I have this code which works from one table:

 

ClientCodes=SELECTCOLUMNS(FILTER('anallink','anallink'[AG_Type]="C"),"Type",'anallink'[AG_Type],"AC_Code",'anallink'[AC_Code],"PCT",'anallink'[AL_PCT])

 

I need to bring in another field based on the AL_PCT field, is it possible to do this? 

 

Thanks 

 

Mick

 

Need Help
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    Hi micklowe ,

     

    According to your description, I made a sample and here is my solution.

    I wonder whether I understand your requirement correctly, please follow steps below.

    Sample data:

    Create a new table as what you wrote.

    ClientCodes =
SELECTCOLUMNS (
    FILTER ( 'TABLE_A', 'TABLE_A'[AG_TYPE] = "C" ),
    "Type", 'TABLE_A'[AG_TYPE],
    "AC_CODE", 'TABLE_A'[AC_CODE],
    "PCT", 'TABLE_A'[AL_PCT]
)

    Then you can establish a relationship between TABLE_B and new table.

    Create a calculated column in the new table.

    Column =
RELATED ( TABLE_B[OTHER] )

    Final output:

     

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ xiaosun

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

1 Reply

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

    Hi micklowe ,

     

    According to your description, I made a sample and here is my solution.

    I wonder whether I understand your requirement correctly, please follow steps below.

    Sample data:

    Create a new table as what you wrote.

    ClientCodes =
SELECTCOLUMNS (
    FILTER ( 'TABLE_A', 'TABLE_A'[AG_TYPE] = "C" ),
    "Type", 'TABLE_A'[AG_TYPE],
    "AC_CODE", 'TABLE_A'[AC_CODE],
    "PCT", 'TABLE_A'[AL_PCT]
)

    Then you can establish a relationship between TABLE_B and new table.

    Create a calculated column in the new table.

    Column =
RELATED ( TABLE_B[OTHER] )

    Final output:

     

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ xiaosun

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