Matching values in 2 table

[josborne]

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

[Anonymous · Accepted solution]

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]

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

[Anonymous]

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.

[pdlv]

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

[josborne]

OK thanks mate I'll give that a try.