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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Broderskap777
Frequent Visitor

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

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? 

Broderskap777_0-1652895704967.png

Thank you 🙂 

 

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

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

 

SpartaBI_0-1652898936298.png

 




2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

7 REPLIES 7
SpartaBI
Community Champion
Community Champion

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

 

SpartaBI_0-1652898936298.png

 




2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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. 

Broderskap777_0-1652902874762.png

 

 

 

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.

@Broderskap777 replace the LEFT with RIGHT 🙂

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

thank you, worked like a charm!

@Broderskap777 my pleasure 🙂

tamerj1
Super User
Super User

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

 

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. 

Broderskap777_0-1652903003725.png

 

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors