How to count distinct values in a column?

[JChris]

I have the following table in PowerBI:

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?

[Ashish_Mathur]

Hi @JChris,

Try this calculated field formula

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

Hope this helps.

[TomMartens]

Hey,

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

Regards

Tom

[JChris]

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.

[v-huizhn-msft]

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

[TomMartens · Accepted solution]

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

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