Forum Discussion
Flag transactions based on multiple rows
Hi, I'm trying write a DAX measure to flag transactions based on the account number and whether the amount is a debit or a credit. I've pasted some sample data with the desired result below. The criteria for flagging a TxNo as unusual is if [Acct] starts with a 4 and [Credit] <> 0, AND there is another line with that TxNo that has a [Debit] <> 0 to any [Acct] that is not 1501, 1601, or starts with "10".
In other words, for each TxNo, if there's a Credit to an Acct starting with a 4, AND a Debit to any account that starts with 10, or equals 1501 or 1601, it should be marked as not unusual on each row of the TxNo. A Debit to any other Acct is unusual and should be marked Yes.
| Desired Result | ||||
| TxNo | Acct | Debit | Credit | UnusualEntry |
| 1 | 4000 | 0 | 500 | No |
| 1 | 1000 | 500 | 0 | No |
| 2 | 4000 | 0 | 400 | Yes |
| 2 | 2000 | 400 | 0 | Yes |
| 3 | 4000 | 0 | 600 | Yes |
| 3 | 1000 | 450 | 0 | Yes |
| 3 | 5000 | 150 | 0 | Yes |
| 4 | 2000 | 100 | 0 | No |
| 4 | 1000 | 0 | 100 | No |
| 4 | 1000 | 200 | 0 | No |
| 4 | 2000 | 0 | 200 | No |
| 5 | 4003 | 0 | 1000 | No |
| 5 | 1501 | 1000 | 0 | No |
| 6 | 4001 | 0 | 750 | Yes |
| 6 | 1500 | 150 | 0 | Yes |
| 6 | 1001 | 600 | 0 | Yes |
| 7 | 4000 | 0 | 250 | Yes |
| 7 | 1502 | 250 | 0 | Yes |
Anonymous,
Try this measure:
Unusual Entry = VAR vTxNo = MAX ( Table1[TxNo] ) VAR vDebit = FILTER ( ALL ( Table1 ), Table1[TxNo] = vTxNo && Table1[Debit] <> 0 && NOT ( LEFT ( Table1[Acct], 2 ) = "10" || Table1[Acct] IN { "1501", "1601" } ) ) VAR vCredit = FILTER ( ALL ( Table1 ), Table1[TxNo] = vTxNo && Table1[Credit] <> 0 && LEFT ( Table1[Acct], 1 ) = "4" ) VAR vResult = IF ( COUNTROWS ( vDebit ) > 0 && COUNTROWS ( vCredit ) > 0, "Yes", "No" ) RETURN vResult
1 Reply
Anonymous,
Try this measure:
Unusual Entry = VAR vTxNo = MAX ( Table1[TxNo] ) VAR vDebit = FILTER ( ALL ( Table1 ), Table1[TxNo] = vTxNo && Table1[Debit] <> 0 && NOT ( LEFT ( Table1[Acct], 2 ) = "10" || Table1[Acct] IN { "1501", "1601" } ) ) VAR vCredit = FILTER ( ALL ( Table1 ), Table1[TxNo] = vTxNo && Table1[Credit] <> 0 && LEFT ( Table1[Acct], 1 ) = "4" ) VAR vResult = IF ( COUNTROWS ( vDebit ) > 0 && COUNTROWS ( vCredit ) > 0, "Yes", "No" ) RETURN vResult