Forum Discussion

adbm's avatar
adbm
Icon for Helper I rankHelper I
2 years ago
Solved

using switch to evaluate reversal entries

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.#NameInvoice No.# Transaction Amount 
600754aaaa123366
989111bbbb234320
242001cccc86030242
242001cccc86030-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
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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.

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      adbm
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        1 year ago

        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. 

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

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