Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JSData
Regular Visitor

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

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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]))

vxinruzhumsft_0-1672277757795.png

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.

View solution in original post

4 REPLIES 4
v-xinruzhu-msft
Community Support
Community Support

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]))

vxinruzhumsft_0-1672277757795.png

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.

FreemanZ
Super User
Super User

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]
)
JSData
Regular Visitor

@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
Arul
Super User
Super User

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors