Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create a new flag column with subquery filtering

Team, Can someone please help me with the below request?
I have a SalesTransaction table like below. I need to create a Flag column with this condition.

 

Condition: First I have to find the Transaction IDs for Account ID = 3 and Flag it as TRUE and Also, I have to check if those transactions IDs  (in Account ID =3) are repeating for any other Account IDs. If yes, I have to Flag those rows also as TRUE.

 

I'm able to find those records in SQL with the below code

 

SELECT * FROM SalesTransaction 
WHERE TRANSACTION ID  IN (select TRANSACTION ID from [SalesTransaction] WHERE
[ACCOUNT ID] = 3)

 

 

Account IDTransaction IDTransaction AmountFlag
1234387TRUE
13453456TRUE
14565644FALSE
2987345TRUE
28765677FALSE
323468956TRUE
33454567TRUE
3987567TRUE
4123457FALSE
42345678TRUE

 

Not sure how to do this in Power BI. Can someone please help me with this.

Thanks!! 

Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    You can try to  create a calculated column as below and apply it as a visual filter.

    New Flag =
VAR _tab1 =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( SalesTransaction ), SalesTransaction[ACCOUNT ID] = 3 ),
        SalesTransaction[TRANSACTION ID]
    )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                _tab1,
                SalesTransaction[Transaction ID] = EARLIER ( SalesTransaction[Transaction ID] )
            )
        ) > 0,
        TRUE (),
        FALSE ()
    )

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You can try a measure like

    measure =
    var _tab = summarize(filter(allselected(SalesTransaction),Table[ACCOUNT ID] = 3), Table[TRANSACTION ID])
    return
    countrows(filter(SalesTransaction,Table[SalesTransaction] in _tab))

     

    or use this as a visual level filter and check for not blank

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey amitchandak Thanks for your quick response. Can you please check the last line of your code please. I think I'm missing something. I didn't get Table[SalesTransaction] and more over I want to create it as a new Flag column.

      Thanks!!

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Anonymous , Sorry, My mistake.

        Please check now

         

        measure =
        var _tab = summarize(filter(allselected(SalesTransaction),Table[ACCOUNT ID] = 3), Table[TRANSACTION ID])
        return
        countrows(filter(SalesTransaction,SalesTransaction[TRANSACTION ID] in _tab))