Forum Discussion

JChris's avatar
JChris
Icon for Helper II rankHelper 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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi JChris,

 

Try this calculated field formula

 

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

 

Hope this helps.