Forum Discussion

CCConsulting's avatar
CCConsulting
Regular Visitor
1 year ago
Solved

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

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...
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    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.

mdaatifraza5556's avatar
mdaatifraza5556
Icon for Super User rankSuper User
1 year ago

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's avatar
CCConsulting
Regular Visitor
1 year ago

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's avatar
    CCConsulting
    Regular Visitor
    1 year ago

    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!