Forum Discussion

Broderskap777's avatar
Broderskap777
Frequent Visitor
4 years ago
Solved

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 numb...
DAX
DAX FILTER
LOOKUPVALUE
match value
Substring
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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


         

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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

 

  • Broderskap777's avatar
    Broderskap777
    Frequent Visitor
    4 years ago

    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.