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...
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.

     
     
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.