Forum Discussion
Helper IIHow 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 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?
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
5 Replies
- TomMartens
Super User
Hey,
can you please make sample data, I have to admit that I do not understand what you are askinf for.
Regards
Tom
JChris2017-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
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
- Ashish_Mathur
Hi JChris,
Try this calculated field formula
=CALCULATE(COUNTROWS(Data),FILTER(VALUES(Data[Host]),COUNTROWS(Data)>1))
Hope this helps.
