topic Re: Add column or replace value? in DAX Commands and Tips

Add column or replace value?

JSData — Wed, 28 Dec 2022 17:51:42 GMT

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.

Account	Sub Account	Invoice Number	Amount
236	00000	5555	-10
526	12345	5555	20
205	00000	5555	30

Re: Add column or replace value?

Arul — Wed, 28 Dec 2022 19:58:02 GMT

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

Re: Add column or replace value?

JSData — Wed, 28 Dec 2022 21:01:39 GMT

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.

Account	Sub Account	Invoice Number	Amount	Desired result
236	00000	5555	-10	12345
526	12345	5555	20	12345
205	00000	5555	30	00000
236	00000	5554	-10	12343
526	12343	5554	20	12343
205	00000	5554	30	00000
526	12333	5253	20	12333

Re: Add column or replace value?

FreemanZ — Thu, 29 Dec 2022 00:44:34 GMT

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]

)

Re: Add column or replace value?

Anonymous — Thu, 29 Dec 2022 01:36:44 GMT

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.