help for chekc if colum are conrelated

[snifer]

id	reference	amount	result	match
100	milan	150	150	no
101	italy	520	520	no
102	100	50	150-50=100	102-100
103	101	100	520-100=420	103-101
104	france	200	200	no

in my table, I have a reference column and an Id column I need to check if the reference number match any id in other all row, and if in the match subtract the column result as shown above

and create a new column where is written what match was find

it will be nicer to create a new table with just the positive match

102	100	50	150-50=100	102-100
103	101	100	520-100=420	103-101

[Zubair_Muhammad]

@snifer

Try these calc columns. Then you can easily filter them using "no match" criterio

See file attached as well

Result_ =
VAR check =
    CONTAINS ( Table1, [id], [reference] )
RETURN
    IF (
        check,
        LOOKUPVALUE ( Table1[ amount], [id], [reference] ) - [ amount],
        [ amount]
    )

Match_ =
VAR check =
    CONTAINS ( Table1, [id], [reference] )
RETURN
    IF ( check, [id] & "-" & [reference], "no" )

 

[snifer]

@Zubair_Muhammad

I got an error when I create first column since the column reference is a txt column( if i try to convert in number i get error)

there any way to filter out text or change the column in integer number without errors?

[Zubair_Muhammad]

@snifer

Could you change your ID column to text type?

Then this formula will work

[snifer]

yes now it is working,

only thing it is missing it to show both of the value on the result

 meaning now i go te Result_ but i dont see how result it came

i need to display the value from id( this is simply, just drag the amount  of the  id) but the problem it is to display the amount of the reference

[Zubair_Muhammad · Accepted solution]

@snifer

Change it to following

Result_ =
VAR check =
    CONTAINS ( Table1, [id], [reference] )
VAR answer =
    IF (
        check,
        LOOKUPVALUE ( Table1[ amount], [id], [reference] ) - [ amount],
        [ amount]
    )
RETURN
    IF ( check, [reference] & "-" & [ amount] & "=" & answer, [ amount] & "" )

[snifer]

@Zubair_Muhammad

the calculation is working but instead of report the amount of the id for example here 52080 it is writing 52080

can be fixed to show instead of the id the amount connect to this id?

the calculation working fine it take in consideration the amount