Forum Discussion

josborne's avatar
josborne
Helper I
2 years ago
Solved

Matching values in 2 table

Hi all.  I've looked for a similar solution ans wasn't able to find one.  Here's my question:

 

I have 2 tables imported into a BI model (Table1, Table2).  Each contains customer information and in my model I have them linked by Account#.  Using Table1 as my reference table, I would like to find matches from Table2 and return a couple of columns from Table2. Sample below, including a hypothetical 'Not Found'.

Additionally, I would like to control the data using dropdowns for financial year/month. I can't work out a way to make this possible.  For example the 'master' list of Table 1 would remain constant by the results from Table2 are affected by a filter.

 

Thanks for anyone who could assist.

 

 

James

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi josborne ,
    Thank you pdlv  very much for the solution, and I'll continue your line of thought and offer him specific advice:

    After determining the relationship between the two tables, you can try to merge them into a single table for the operation.

    MergedTable = 
ADDCOLUMNS(
   ALL('Table'),
   "Customer2",
    LOOKUPVALUE('Table (2)'[Customer],'Table (2)'[Account#],'Table'[Account#],"not found"),
    "Account2",
    LOOKUPVALUE('Table (2)'[Account#],'Table (2)'[Account#],'Table'[Account#],BLANK()),
    "Value2",
    LOOKUPVALUE('Table (2)'[Value],'Table (2)'[Account#],'Table'[Account#],BLANK()),
    "financialYear1",'Table'[FinancialYear])

    As for the second question you are trying to fulfill, I added years to your dataset to try to make what you need.

    Creating year tables that create links to merged tables can accomplish what you need.

    FinancialYearTable = DATATABLE("Year",INTEGER,
{
    {2021},{2022}
})

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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

     
     

5 Replies

  • pdlv's avatar
    pdlv
    Helper I
    2 years ago

    I think you should try to merge 2 table in to one then use the merged table to filter or caculate.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi josborne ,
    Thank you pdlv  very much for the solution, and I'll continue your line of thought and offer him specific advice:

    After determining the relationship between the two tables, you can try to merge them into a single table for the operation.

    MergedTable = 
ADDCOLUMNS(
   ALL('Table'),
   "Customer2",
    LOOKUPVALUE('Table (2)'[Customer],'Table (2)'[Account#],'Table'[Account#],"not found"),
    "Account2",
    LOOKUPVALUE('Table (2)'[Account#],'Table (2)'[Account#],'Table'[Account#],BLANK()),
    "Value2",
    LOOKUPVALUE('Table (2)'[Value],'Table (2)'[Account#],'Table'[Account#],BLANK()),
    "financialYear1",'Table'[FinancialYear])

    As for the second question you are trying to fulfill, I added years to your dataset to try to make what you need.

    Creating year tables that create links to merged tables can accomplish what you need.

    FinancialYearTable = DATATABLE("Year",INTEGER,
{
    {2021},{2022}
})

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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

     
     
    • josborne's avatar
      josborne
      Helper I
      2 years ago

      Thanks for that detail!  Very much appreciated.  So in your example, 'Table' is my 'Table1' and 'Table 2' is my 'Table2' right? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi josborne ，
        I think so, all the data I'm using is operating on the data you've given me, so hopefully you'll be able to implement what you've got in mind.

        Hope it helps!

         

        Best regards,
        Community Support Team_ Tom Shen

         

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