Reply
Frequent Visitor
Posts: 3
Registered: ‎10-08-2017
Accepted Solution

Flagging duplicate fields

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


Accepted Solutions
Super Contributor
Posts: 663
Registered: ‎01-14-2018

Re: Flagging duplicate fields

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

4.PNG

 

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.

View solution in original post


All Replies
Super User
Posts: 1,830
Registered: ‎02-28-2017

Re: Flagging duplicate fields

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
)

 

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Super Contributor
Posts: 663
Registered: ‎01-14-2018

Re: Flagging duplicate fields

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

4.PNG

 

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.