Filter the count of distinct counts

[peps984]

Hi guys,

I have a table like this, imported from a CSV (I'm sorry I've not been able to render the table better than this):

item	year	column3	column4
alpha	1980
beta	2000
gamma	2005
alpha	2000
alpha	2002
gamma	1999
beta	2000
beta	2000
alpha	1980

Column3 and 4 contain other data not so relevant to the thread.

I need to calculate how many items have a distinct count for year > 1.

With this data, the result would be 2, because for item "alpha" the distinct count is 3, for "beta" it's 1 and for "gamma" it's 2.

I can easily calculate it with a table visual, but I need a card, just the number. I've tried to come up with a solution, but I can't figure out where to start

Could you please help me?

[ryan_mayu · Accepted solution]

@peps984 

you can try this

Measure = 
VAR _tbl=SUMMARIZE('Table','Table'[item],"count",DISTINCTCOUNT('Table'[year]))
return COUNTROWS(FILTER(_tbl,[count]>1))

[peps984]

yes!! 😀 this is what I was looking for.

Thanks for your help in finding the solution and the logic behind it 😊

[ryan_mayu]

you are welcome