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!
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!
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

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.

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 410 members 3,978 guests
Please welcome our newest community members: