Dax: Distintcount values based on other row repetition

[Anonymous]

Hi,

I have the following table 

User	Country
1	Germany
2	France
3	Spain
4	Germany
5	Spain
6	Germany
7	Italy

I would like to count the distinct users that belong to countries where there are more users (so duplicated countries)

In this example, users from Germany and Spain.

I was able to count the repeated countries with something like this...

COUNTROWS(

          FILTER(

                    DISTINCT(country),CALCULATE(DISTINCTCOUNT(User)>1)))

...but I need to count the distinct users. I also thought of doing a summarize, but the table is too big and I'm worried about the performance with it.

Any help is more than welcome.

Thanks in advance!!

Javi

[Anonymous]

@Anonymous  Hey Mate ,
You can use this function 
User = 

COUNTROWS(

          FILTER(

                    values(country),
                    CALCULATE(DISTINCTCOUNT(User)>=1)
                  )
                 )
or

COUNTROWS(

          FILTER(

                    values(country),
                    CALCULATE(DISTINCTCOUNT(User))
                  )
                 )

Thank you 

[amitchandak]

@Anonymous , try like

countx(filter(summarize(Table, Table[country], "_1",DISTINCTCOUNT(Table[User])),[_1]>1 ),[country])

[Anonymous · Accepted solution]

Hi, thanks to both of you for the support.

I think @amitchandak approach is the right one but I want the users instead of the countries and to Sum them instead of the Count because you are aggregating them in that summarize.

This is currently working.

SUMX( FILTER( SUMMARIZE( Location, Location[Country], "Users",DISTINCTCOUNT(Location[User]) ) ,[Users]>1 ) ,[Users])

Thanks a lot!!