Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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!! 

1 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.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@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

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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!!

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

@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
Not applicable

@amitchandak - Any update on my above request?

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
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.