Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 crit...
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    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

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

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