Forum Discussion

JChris's avatar
JChris
Helper II
8 years ago
Solved

How to count distinct values in a column?

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 ...
Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

JChris's avatar
JChris
Helper II
8 years ago

2017-10-09.csv
to,cc,hostname
[email protected],[email protected],US001
[email protected],[email protected],US017
[email protected],[email protected],US032
...

2017-10-10.csv
to,cc,hostname
[email protected],[email protected],US001
[email protected],[email protected],US017
[email protected],[email protected],US037
...

 

combined

to,cc,hostname
[email protected],[email protected],US001
[email protected],[email protected],US017
[email protected],[email protected],US032
[email protected],[email protected],US001
[email protected],[email protected],US017
[email protected],[email protected],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, [email protected] and [email protected] 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.

TomMartens's avatar
TomMartens
Super User
8 years ago

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