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] ) )
)
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 ()
    )
)
      • Colinu's avatar
        Colinu
        Helper I
        8 years ago

        HI Zubair 

         

        that did the trick thanks.

         

        it has however shown one more issue. in one of the column i need to count there are quantities. 

         

        ScanKitBag           Crabs

        12                            2

        13                            4

        14                            2

        15                            2

        16                            4

        16                            4

         

        In this instance i want to sum [Crabs] but exclude the duplicate bag numbers (16 counted twice.) So the out come of this instance would be 14 and not 18.