0

I have a transactional dataset (Table). I want to identify rows that have the same ID and have amounts that are voided (or canceled). Basically what I want is a new column, "Voided", that looks something like: 

ID      Amount    Voided
1001    10        1
1001    -10       1
1001    -10       0
1002    25        0
1002    25        0  
1003    20        0

I've tried the following code as a new column:

Voided = 
VAR void =
    COUNTROWS ( FILTER (  ALL ( Table' ),
               'Table'[ID] = EARLIER ( 'Table'[ID] )
                 && (   'Table'[Amount] = ( -1*(EARLIER ( 'Table'[Amount] )))
                    ||  'Table'[Amount] = ( 1*(EARLIER ( 'Table'[Amount] )))
                    )
                 ))
RETURN    IF ( void >= 2, 1, 0 )

However, this doesn't work because I end up with the following: 

ID      Amount    Voided
1001    10        1
1001    -10       1
1001    -10       1
1002    25        1
1002    25        1  
1003    20        0
Milo
  • 3,365
  • 9
  • 30
  • 44
Emily S
  • 1

1 Answers1

0

I ultimately found a solution by creating three 'temporary' columns and an index column. I later hid these temporary columns in the report view. To create an index column go to this StackOverflow solution: creating an index column for Power BI.

Temporary column 1 is calculated as follows:

Voided_temp = 
VAR void =
// identifies the voided row (only the negative).
COUNTROWS ( FILTER ( ALL ( 'Table' ),
'Table'[ID] = EARLIER ( 'Table'[ID] )
&& ( -1*('Table'[Amount]) = (EARLIER ( 'Table'[Amount] ))
&& ('Table'[Amount] +  (EARLIER ( 'Table'[Amount] )) = 0)
)
))
RETURN IF ( void >= 2, 1, 0 )

Temporary column 2 is calculated as follows: 

Voided_temp2 = 
// identifies all the positive rows of the voided out parcels (voided_temp).
IF( COUNTROWS( FILTER('Table', 
    ('Table'[ID] = EARLIER ( 'Table'[ID] )
        && -1*('Table'[Amount]) = ( EARLIER ( 'Table'[Amount] ))
        && 'Table'[Voided_temp] = 1
        ))), 1, 0)
//        && 'Table'[Index] > EARLIER('Table'[Index]))

Temporary column 3 is calculated as follows: 

Voided_temp3 = 
// identifies the first positive row (voided_temp2) of the voided out parcels (voided_temp).
IF( 'Table'[Voided_temp2] = 1
    &&  COUNTROWS( FILTER('Table',     
    ('Table'[ID] = EARLIER ( 'Table'[ID] )
    && 'Table'[Amount] = ( EARLIER ( 'Table'[Amount] ))
    && 'Table'[Index] > EARLIER ('Table'[Index])
))), 1, 0)

Finally, I created the voided column I was in search of by a simple if function. The voided column was created as follows: 

Voided = 
IF('Table'[Voided_temp] = 1 
    || 'Table'[Voided_temp3] = 1
, 1, 0)

This was a roundabout way of finding a solution in Power BI, but it worked. Would love to figure out a simpler way to do this. Any input is greatly appreciated! Thanks.

Emily S
  • 1