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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors