Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Colinu
Helper I
Helper I

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

1 ACCEPTED SOLUTION

@Colinu

 

And this

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

 

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Colinu

 

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

Regards
Zubair

Please try my custom visuals

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.

 

 

@Colinu

 

Try this

 

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

Regards
Zubair

Please try my custom visuals

@Colinu

 

And this

 

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

Regards
Zubair

Please try my custom visuals

Thanks for this. One little thing is how do i make this a whole number without decimal points? 

@Colinu

 

Select the MEASURE from FIELDS>>> Go to Modelling Tab>>>Formatting and select whole number

 

FormatMeasure.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.