Create a new flag column with subquery filtering

[Anonymous]

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 ID	Transaction ID	Transaction Amount	Flag
1	234	387	TRUE
1	345	3456	TRUE
1	456	5644	FALSE
2	987	345	TRUE
2	876	5677	FALSE
3	234	68956	TRUE
3	345	4567	TRUE
3	987	567	TRUE
4	123	457	FALSE
4	234	5678	TRUE

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

Thanks!! 

[amitchandak]

@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]

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]

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

[Anonymous]

@amitchandak - Looks like your measure is working. But, since it is a measure I'm not able to use it as Slicer.
Also, If I use this measure as a visual filter, I notice a huge performance issue. The table taking very long time to load everytime.

Thanks!

[Anonymous]

@amitchandak - Any update on my above request?

[v-easonf-msft · Accepted solution]

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.

[Anonymous]

Hey @v-easonf-msft - This is exactly what I'm looking for. Thanks so much!