Flagging duplicate fields

[abrasington]

Hi all,

I have a table with every row describing a single quantity of different types of equipment.

Some rows are meant to have a unique item ID code, and I would like to first display all rows of only a certain type of equipment and then make sure that each row of said equipment isn't duplicating any item ID codes. 

I'm playing around with using DISTINCT, but so far no luck. Any good ideas of how to tackle this?

Thanks

[v-xjiin-msft · Accepted solution]

Hi @abrasington,

In your scenario, did you mean this?

We can create a calculated column to count the number of ItemID based on equipment type group. And if the number is more than 1, obviously there exists duplicate item id.

Count ItemID =
CALCULATE (
    COUNT ( equipment[ItemID] ),
    ALLEXCEPT ( equipment, equipment[equipment] )
)

And if you want to check a certain type of equipment. You can do this:

Count A1 =
CALCULATE (
    COUNT ( equipment[ItemID] ),
    FILTER ( equipment, equipment[equipment] = "A1" )
)

Thanks,
Xi Jin.

[Anonymous]

My immediate thought is you could create a calculated column that does a count of each row and how often a particular field appears. You could then filter on that number.

Something like 

AppearCount = VAR searchStr = [YourField]
RETURN
Calculate(
         COUNTROWS('MyTable'),
         'MyTable'[YourField] = searchStr
)