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

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.

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Colinu

         

        Try this

         

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