cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 145 members 1,910 guests
Please welcome our newest community members: