Add column in (one) table with max value from another table (many)

[CCConsulting]

Would greatly appreicate help with this. I want to add a column to Table 1 that inserts the maximum value of "Invoice #" from that Account/Customer's multipe rows in Table 2.  Next I want to populate the third row of Table 1 through look-up to get the "New Charges" value from Table 2 associated with the "Most Recent (max) Invoice" row that we just found (I'll have to match "Customer Number" & "Account Number" and also "Most Recent Invoice" & "Invoice #".  I've entered the red values manually to show the desired result. The two tables share the relationship Table 1 (Account Number) == Table 2 (Customer Number). Thanks in advance for any recommendations. 

TABLE 1 Account Number	Most Recent (max) Invoice #	New Charges from Most Recent Invoice
123	AC	30
456	AD	40

TABLE 2 Customer  Number	Invoice #	Balance	New Charges
123	AB	50	50
123	AC	80	30
123	AA	50	50
456	AD	40	40
456	AA	40	40
456	AB	70	30
456	AC	40	40

[v-sshirivolu]

Hi @CCConsulting ,
Thank you for reaching out to Microsoft Fabric community.

Try these Steps - 

Create a Relationship

Table1[Account Number] → Table2[Customer Number]

Add Column for Most Recent Invoice in Table 1

Most Recent (max) Invoice = 
CALCULATE (
    MAX ( Table2[Invoice] ),
    FILTER (
        Table2,
        Table2[Customer Number] = Table1[Account Number]
    )
)
 

Add Column to Get New Charges for Most Recent Invoice

New Charges from Most Recent Invoice = 
VAR RecentInvoice = Table1[Most Recent (max) Invoice]
RETURN
CALCULATE (
    MAX ( Table2[New Charges] ),
    FILTER (
        Table2,
        Table2[Customer Number] = Table1[Account Number]
            && Table2[Invoice] = RecentInvoice
    )
)
 
Output - 

Please refer the attached .pbix file for your reference.
Regards,
Sreeteja.

[mdaatifraza5556 · Accepted solution]

Hi @CCConsulting 

Can you please follow the below steps and use the dax to get your requirement ?

Since Invoice #  is in text format, I am assuming the values are sequential alphabetically.

1. Create a calculated column on table1.

Most Recent Invoice # =

CALCULATE(

    MAX('Table2'[Invoice #]),

    FILTER(

        'Table2',

        'Table2'[Customer Number] = 'Table1'[Account Number]

    )

)



2. Create final calculated column in table1.

New Charges from Most Recent Invoice =

LOOKUPVALUE(

    'Table2'[New Charges],

    'Table2'[Customer Number], 'Table1'[Account Number],

    'Table2'[Invoice #], 'Table1'[Most Recent Invoice #]

)

If this answers your questions, kindly accept it as a solution and give kudos.

[CCConsulting]

Thanks so much, this yielded exactly what I wanted - BUT when I tried to replicate the Look-Up for an additional column I got the error "A table of multiple values was supplied where a sing value was expected"

I used the DAX code from above for both -only changing the name and the column to pull from. Any ideas what might be the problem? 

Thank you for your time and insight!

Here are my two New Columns (using my actual file/column names)

This one works:

Latest ACRN Expenditures =

LOOKUPVALUE(

    RadGridExport[Expended],

    RadGridExport[SDN], 'Active Grants New'[Award Number w/o dash],

    RadGridExport[ACRN], 'Active Grants New'[Most recent ACRN]

)

This one produces the error "A table of multiple values was supplied where a single value was expected"

Latest ACRN Unexpended =

LOOKUPVALUE(

    RadGridExport[Unexpended],

    RadGridExport[SDN], 'Active Grants New'[Award Number w/o dash],

    RadGridExport[ACRN], 'Active Grants New'[Most recent ACRN]

)

[CCConsulting]

Ah - I tried a new approach - more thinking and less clicking! I looked again at my Table 2 and of the 26,000 rows I had 27 duplicates. After removing them I get both columns populated.  I'm not sure why that wouldn't have shown up in the first column and only the second - I guess there is a remote chance that those values were the same and so didn't appear to be a "table of values" where there were perhaps different values in that column of the second case... Something to look into.  But thanks again for your time and extremly clear answer/example!