Forum Discussion

Helper I

8 years ago

Solved

Counting distinct (unique) values

I have been trying to count the values in a column. I have tried the belwo

Count Kit Bags = DISTINCTCOUNT(PPECheckinOut[ScanKitBagTag])

the result should be 16 but it reuturns 17 (counting the duplicate value).

can anyone help as to why or how to get it to count only values that are unique and not balnk.

Thanks

Zubair_Muhammad

And this

Measure 2 =
SUMX (
    ALL ( PPECheckInOut[ScanKitBag ], PPECheckInOut[ Crabs] ),
    CALCULATE ( VALUES ( PPECheckInOut[ Crabs] ) )
)

11 Replies

Greg_Deckler
Community Champion
8 years ago
Sample data would be immensely helpful in this case. A couple things, perhaps try to Trim your data on import as well as make sure everything has the same case. To exclude blanks, wrap your aggregation in a CALCULATE clause and filter out the blanks.
- Colinu
  Helper I
  8 years ago
  HI Smoupre
  
  thanks fory your reply. The Data in column looks like this.
  
  ScanKitbagTag
  Bag 1
  Bag 2
  Bag 3
  Bag 4
  Bag 5
  Bag 6
  Bag 7
  Bag 8
  Bag 9
  Bag 10
  Bag 11
  Bag 12
  Bag 13
  Bag 14
  Bag 15
  Bag 16
  Bag 15
  
  As you can see Bag 15 is here twice. as the data is collated it will always contain duplication. i wish only to count the amount of uniquie values. in this instance it should be 16.
  - Greg_Deckler
    Community Champion
    8 years ago
    OK, I used this Enter Data query:
    
    let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckpMVzBUitWBsIzgLGM4ywTOMoWzzOAsczjLAs6yhLMMDRBMhC2GCGsMEfYYIiwyRNhkaIYiGgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ScanKitbagTag = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ScanKitbagTag", type text}}) in #"Changed Type"
    And this measure:
    
    Measure 12 = DISTINCTCOUNT(ScanKitBagTag[ScanKitbagTag])
    And got 16.