Forum Discussion

JSData's avatar
JSData
Frequent Visitor
3 years ago
Solved

Add column or replace value?

Hello, I'm having some trouble puting a formula together to add a column that will return Sub Account 12345 for the first row in the table below. The account number for the department that needs to be replaced will always be 236. 

AccountSub AccountInvoice NumberAmount
236000005555-10
52612345555520
20500000555530

 

DAX
LOOKUPVALUE
same table
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi JSData 

    You can create a new column:

    Column = var a=FILTER('Table',[Invoice Number]=EARLIER('Table'[Invoice Number]))
return IF([Amount]>0,[Sub Account],MAXX(a,[Sub Account]))

    Best Regards!

    Yolo Zhu

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

4 Replies

  • Arul's avatar
    Arul
    Super User
    3 years ago

    JSData ,

    try this formula in calculated column,

    Sub account new =
VAR _value = 12345
VAR _result =
    IF ( 'Test Table'[Account] = 236, _value, 'Test Table'[Sub Account] )
RETURN
    _result

    Thanks,

    Arul

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi JSData 

    Not sure if i fully get you, try to create a column like this:
    Result =
    VAR _invoice = [Invoice Number]
    RETURN
    MAXX(
        FILTER(
             data, 
             data[Invoice Number]=_invoice
        ),
        data[Sub Account]
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi JSData 

    You can create a new column:

    Column = var a=FILTER('Table',[Invoice Number]=EARLIER('Table'[Invoice Number]))
return IF([Amount]>0,[Sub Account],MAXX(a,[Sub Account]))

    Best Regards!

    Yolo Zhu

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

  • JSData's avatar
    JSData
    Frequent Visitor
    3 years ago

    Arul 

    I have to apolgize I did not put enough detail in my orignal post. Your solution will work if it's only one replacement. The problem I'm working on is for each invoice number that has this account number I need to correct subaccount to be assigned to that account. I've put an expanded table with a desired result column that hopefully explains this more clearly. Again I apologize for being unclear the first time.

    AccountSub AccountInvoice NumberAmountDesired result
    236000005555-1012345
    5261234555552012345
    2050000055553000000
    236000005554-1012343
    5261234355542012343
    2050000055543000000
    5261233352532012333