Forum Discussion

Colinu's avatar
Colinu
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 th...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Colinu

     

    And this

     

    Measure 2 =
SUMX (
    ALL ( PPECheckInOut[ScanKitBag ], PPECheckInOut[ Crabs] ),
    CALCULATE ( VALUES ( PPECheckInOut[ Crabs] ) )
)
Colinu's avatar
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's avatar
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.

  • Colinu's avatar
    Colinu
    Helper I
    8 years ago

    Thanks

    Smoupre

     

    i am new to BI so relay dont know what to do with the Enter Data Query. could you give me a steer on where this is placed to help with the soultion.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      You can do 2 things. Either create a new blank query and use Advanced Editor to paste in my code. Or, click on Enter Data in the ribbon and copy and paste the data you posted including the column header.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Colinu

         

        Count Kit Bags =
CALCULATE (
    DISTINCTCOUNT ( PPECheckinOut[ScanKitbagTag] ),
    FILTER (
        ALL ( PPECheckinOut[ScanKitbagTag] ),
        PPECheckinOut[ScanKitbagTag] <> BLANK ()
    )
)