topic Re: Calculate amount based on substring, show value on matching row (in a measure) in DAX Commands and Tips

Calculate amount based on substring, show value on matching row (in a measure)

Broderskap777 — Wed, 18 May 2022 17:52:10 GMT

Hi,

I am trying to figure out if there is any way to get the row values from one "Transaction Number" to a another "Transaction Number" in the same column.

For example, I have a transaction number that have som form of amount. Sometimes, there are transactions that have the identical number but start with a "C". The C stands for "Correction" and I would like to get the values from the ones that start with "C" and show the results in a measure but on the row with the corresponding number without a letter. Is this possible to do as measure?

Thank you 🙂

Re: Calculate amount based on substring, show value on matching row (in a measure)

tamerj1 — Wed, 18 May 2022 18:33:17 GMT

Hi Broderskap777

please try

NewMeasure = SUMX ( VALUES ( TableName[Transactio Number] ), CALCULATE ( IF ( RIGHT ( SELECTEDVALUE ( TableName[Transactio Number] ), 1 ) <> "C", CALCULATE ( [Amount], FILTER ( ALL ( TableName ), TableName[Transactio Number] = "C" & SELECTEDVALUE ( TableName[Transactio Number] ) ) ) ) ) )

Re: Calculate amount based on substring, show value on matching row (in a measure)

SpartaBI — Wed, 18 May 2022 18:35:57 GMT

Broderskap777:

New Measure = SUMX( VALUES('Table'[Transaction Number]), VAR _current_tr_num = 'Table'[Transaction Number] VAR _correction = "C" & _current_tr_num VAR _result = CALCULATE([Amount Measure],'Table'[Transaction Number] = _correction) RETURN _result )

Showcase Report – Contoso By SpartaBI

Re: Calculate amount based on substring, show value on matching row (in a measure)

Broderskap777 — Wed, 18 May 2022 19:41:23 GMT

Thank you both for the quick replies. Unfortunately, I can’t get any of the codes to work since I misread my own data. The numbers are not identical, but the first 0 is replaced with a "C" e.g.

For your code @SpartaBI , I tried to alter the code like this (for the second variable):

"C" & LEFT(_current_tr_num,LEN(_current_tr_num)-1) .. But I might be getting the logic wrong.

And for your code @tamerj1 I couldn’t get "selectedvalue" to work (it was greyed out and not found by intellicense) so I replaced it with "hasonevalue", but the code is returning blanks.

I apologize in advance if the solution is simple. but I am a beginner in DAX.

Re: Calculate amount based on substring, show value on matching row (in a measure)

Broderskap777 — Wed, 18 May 2022 19:43:33 GMT

Thank you both for the quick replies. Unfortunately, I can’t get any of the codes to work since I misread my own data. The numbers are not identical, but the first 0 is replaced with a "C" e.g.

For your code @SpartaBI , I tried to alter the code like this (for the second variable):

I apologize in advance if the solution is simple. but I am a beginner in DAX.

Re: Calculate amount based on substring, show value on matching row (in a measure)

SpartaBI — Wed, 18 May 2022 19:57:23 GMT

Broderskap777 replace the LEFT with RIGHT 🙂

Re: Calculate amount based on substring, show value on matching row (in a measure)

Broderskap777 — Wed, 18 May 2022 19:59:06 GMT

hahaha it's amazing how the brain can fail you at times :')

thank you, worked like a charm!

Re: Calculate amount based on substring, show value on matching row (in a measure)

SpartaBI — Wed, 18 May 2022 19:59:59 GMT

Broderskap777 my pleasure 🙂