Forum Discussion

Colinu's avatar
Colinu
Icon for Helper I rankHelper 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
Icon for Community Champion rankCommunity 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
Icon for Community Champion rankCommunity Champion
8 years ago

Colinu

 

Count Kit Bags =
CALCULATE (
    DISTINCTCOUNT ( PPECheckinOut[ScanKitbagTag] ),
    FILTER (
        ALL ( PPECheckinOut[ScanKitbagTag] ),
        PPECheckinOut[ScanKitbagTag] <> BLANK ()
    )
)
  • Colinu's avatar
    Colinu
    Icon for Helper I rankHelper 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
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Colinu

       

      Try this

       

      Measure =
SUMX (
    SUMMARIZE ( PPECheckInOut, PPECheckInOut[ScanKitBag ], PPECheckInOut[ Crabs] ),
    CALCULATE ( VALUES ( PPECheckInOut[ Crabs] ) )
)
      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Colinu

         

        And this

         

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