using switch to evaluate reversal entries

[adbm]

Hi,

I have the following dataset. I want to be able to add a column after to label each row as to whether it is a reversal entry or not.

Vendor ID.#	Name	Invoice No.#	Transaction Amount
600754	aaaa	123	366
989111	bbbb	234	320
242001	cccc	86030	242
242001	cccc	86030	-242

I tried using the switch formula like this: 

IsReversal =

SWITCH(

    TRUE(),

    AND(FILTER('Vendor data','Vendor data'[Reference No.#] = EARLIER('Vendor data'[Reference No.#])),(FILTER('Vendor data','Vendor data'[Transaction Amount] = -'Vendor data'[Transaction Amount]))),"Reversal","Not Reversal")

But Power bi then gives me the following error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

I'm not able to get past this error; it ried removing the filter expression - it didnt work, i tried replacing it with hasonevalue - it didnt work ... im not sure what to do next.

Basically i need the column to show a result as "Reversal" in the rows where invoice number is the same and the transaction amts are the same with one being -ve and the other being a +ve value.

Can someone please help me resolve this error?

Thanks a lot for your help.

Best, Natali

[adbm]

Thanks @rajendraongole1  - this worked.

[bhanu_gautam]

@adbm , Try using below measure

IsReversal =
VAR CurrentInvoice = 'Vendor data'[Invoice No.#]
VAR CurrentAmount = 'Vendor data'[Transaction Amount]
RETURN
IF (
CALCULATE (
COUNTROWS('Vendor data'),
'Vendor data'[Invoice No.#] = CurrentInvoice,
'Vendor data'[Transaction Amount] = -CurrentAmount
) > 0,
"Reversal",
"Not Reversal"
)

[rajendraongole1 · Accepted solution]

Hi @adbm -Can you please try the below calculated column,it's returning multiple rows when it needs a scalar.

check the calculated column

IsReversal =
VAR CurrentInvoice = 'Vendor data'[Invoice No.#]
VAR CurrentAmount = 'Vendor data'[Transaction Amount]
RETURN
IF(
CALCULATE(
COUNTROWS('Vendor data'),
FILTER(
'Vendor data',
'Vendor data'[Invoice No.#] = CurrentInvoice &&
'Vendor data'[Transaction Amount] = -CurrentAmount
)
) > 0,
"Reversal",
"Not Reversal"
)

Hope we will not an error. it works.

[adbm]

Hi @rajendraongole1  - could you please briefly explain how your formula works but my switch one didnt? i guess its a matter of learning DAX but if you could please explain a bit right now, ill know which direction to head in the next time.

[rajendraongole1]

Hi @adbm - Glad it works at your end.  Filter is being used incorrectly in the context of the switch statement, as it's returning multiple rows when it needs a scalar (single value).the logic you want have to be implemented using calculate and filter, which will allow you to check for reversal entries row by row.Let me know if any information required from my end/

Hope the above information helps.