cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JChris Regular Visitor
Regular Visitor

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
Super User

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.
Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User
Super User

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.
Proud to be a Datanaut!
JChris Regular Visitor
Regular Visitor

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
Super User

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.
Proud to be a Datanaut!

View solution in original post

v-huizhn-msft Super Contributor
Super Contributor

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
Super User

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 245 members 2,795 guests
Please welcome our newest community members: