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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AA622
Helper II
Helper II

Duplicates values within 1 column

Hello

 

I am filtering through a table full of sales orders. Over 100,00 rows. I just want to find the sales order numbers that show up more than once in the column and I want to create a column that tells me if there is, or isn't. True/False if you will. I just need to know which sales order#'s are dupicated so i can sort through them for the different PO #'s. Below is the example of what I am looking for.

 

Agency    order#     PO#       Measure/column

AAA         #230         XY              True

AAA         #230         X                True

AAA         #230         MM            True

BB            #165         L                False

BB            #166          T               False

1 ACCEPTED SOLUTION

Hi @AA622,

You can try to use the following calculate column code, I add conditions to check both two fields to return true/false tags:

Tag =
VAR rowcount =
    COUNTROWS (
        FILTER (
            'Table',
            [Agency] = EARLIER ( 'Table'[Agency] )
                && [Order#] = EARLIER ( 'Table'[Order#] )
        )
    )
RETURN
    IF ( rowcount > 1, TRUE (), FALSE () )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@AA622 ,

new column =

var _cnt = countx(filter(Table, [Order#] =earlier([Order#]) ), [Order #])

return

if(_cnt >1, true(), false())

Hello

 

Thanks so much it works but I have a followup question. I sorted the report to only show the 'True' values but I noticed there are still some order#'s that only show up once yet have the 'true' value. Why is that? I wanted it to show only order#'s that are duplicated within the same column

 

Is there a workaround to this?

Hi @AA622,

You can try to use the following calculate column code, I add conditions to check both two fields to return true/false tags:

Tag =
VAR rowcount =
    COUNTROWS (
        FILTER (
            'Table',
            [Agency] = EARLIER ( 'Table'[Agency] )
                && [Order#] = EARLIER ( 'Table'[Order#] )
        )
    )
RETURN
    IF ( rowcount > 1, TRUE (), FALSE () )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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