cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JChris Helper II
Helper II

How to count distinct values in a column?

I have the following table in PowerBI:

 

Capture.PNG

 

The entries come from a Folder source, where I have multiple CSVs with the same structure that gets combined into a single query inside PBI. I may have the same hostname in more than one CSV, so I use the "Count of Hostname" and sort it to see the duplicated hostnames, but I also want to count the number of hostnames where the "count of hostnames" is more than 1, so I can display that in a Card view. How can I do that?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: How to count distinct values in a column?

Hey,

 

based on your sample data I created the following calculated column

MoreThanOnce = 
IF(
	CALCULATE(
		COUNTROWS('Table1')
		,ALLEXCEPT('Table1','Table1'[from])
	) >1
	,"TRUE"
	,"FALSE"
)

This results to

2017-10-10_19-54-03.png

 

Now you can use this colum to slice your data, and also in charts. Putting the column on the xaxis, [from] to the value (change the aggregation to "Count (Distinct)", or create measure like this

distinct froms with multiple appearance = 
CALCULATE(
DISTINCTCOUNT('yourtable'[from])
,'yourtable'[MoreThanOnce] = "TRUE"
)

Hope this is what you are looking for

 

Regards

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User III
Super User III

Re: How to count distinct values in a column?

Hey,

 

can you please make sample data, I have to admit that I do not understand what you are askinf for.

 

Regards

Tom

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
JChris Helper II
Helper II

Re: How to count distinct values in a column?

2017-10-09.csv
to,cc,hostname
alice@company.com,bob@company.com,US001
diana@company.com,willy@company.com,US017
mike@company.com,bob@company.com,US032
...

2017-10-10.csv
to,cc,hostname
alice@company.com,bob@company.com,US001
diana@company.com,willy@company.com,US017
wallace@company.com,stuart@company.com,US037
...

 

combined

to,cc,hostname
alice@company.com,bob@company.com,US001
diana@company.com,willy@company.com,US017
mike@company.com,bob@company.com,US032
alice@company.com,bob@company.com,US001
diana@company.com,willy@company.com,US017
wallace@company.com,stuart@company.com,US037

...


Both CSVs will be inside the folder "input" and I use that folder as source in PBI, using combine + edit, so I have all data in one query with only one header. I then proceed and create the table visual, and add one "count of hostnames" column. In this example, alice@company.com and diana@company.com will have a count of 2 hostnames, as they appear twice in the combined source query.

I want to count how many people have more than 1 as "count of hostname". I already can see who they are, I just want to know how many they are without having to count myself.

Super User III
Super User III

Re: How to count distinct values in a column?

Hey,

 

based on your sample data I created the following calculated column

MoreThanOnce = 
IF(
	CALCULATE(
		COUNTROWS('Table1')
		,ALLEXCEPT('Table1','Table1'[from])
	) >1
	,"TRUE"
	,"FALSE"
)

This results to

2017-10-10_19-54-03.png

 

Now you can use this colum to slice your data, and also in charts. Putting the column on the xaxis, [from] to the value (change the aggregation to "Count (Distinct)", or create measure like this

distinct froms with multiple appearance = 
CALCULATE(
DISTINCTCOUNT('yourtable'[from])
,'yourtable'[MoreThanOnce] = "TRUE"
)

Hope this is what you are looking for

 

Regards

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

Microsoft v-huizhn-msft
Microsoft

Re: How to count distinct values in a column?

Hi @JChris,

The solution @TomMartens posted is right, you should create a calculated column "count of hostnames" in sample table rather than in table visualization.

count of hostnames =
CALCULATE ( COUNTROWS ( 'Table1' ), ALLEXCEPT ( 'Table1', 'Table1'[from] ) )


Then create a measure using the formula and display it in card visual.

multiple appearance =
CALCULATE (
    DISTINCTCOUNT ( 'yourtable'[from] ),
    FILTER ( 'yourtable', 'yourtable'[MoreThanOnce] > 1 )
)


Best Regards,
Angelia

Highlighted
Super User IV
Super User IV

Re: How to count distinct values in a column?

Hi @JChris,

 

Try this calculated field formula

 

=CALCULATE(COUNTROWS(Data),FILTER(VALUES(Data[Host]),COUNTROWS(Data)>1))

 

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors