cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

How to show the most representative of users country in a project

I have this table
I would like to add a column (desired result) that shows the country with the most users participating in that project for each project
As example, for project 1 I have 3 users in CA and 2 in US. "Desired Result" will then show CA for all users

 UsersName UsersCountry Project DesiredResult User1 CA Project1 CA User2 US Project1 CA User3 CA Project1 CA User4 CA Project1 CA User5 CA Project1 CA User1 CA Project2 US User2 US Project2 US User3 CA Project2 US User4 US Project2 US User6 US Project2 US User1 CA Project3 AU User3 CA Project3 AU User6 US Project3 AU User7 AU Project3 AU User8 AU Project3 AU User9 AU Project3 AU User10 AU Project3 AU User11 AU Project3 AU User12 AU Project3 AU User13 AU Project3 AU
3 ACCEPTED SOLUTIONS
Community Champion

``````Desired Result =
VAR _current_project = Table[Project]
VAR _tbl_1 =
FILTER(
Table,
Table[Project] = _current_project
)
VAR _tbl_2 =
GROUPBY( _tbl_1,Table[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _desired_country =
CONCATENATEX(
FILTER(
_tbl_2,
[@Users] = _max_per_country
),
Table[UsersCountry], ", "
)
RETURN
_desired_country``````

In case it solved your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

Community Champion

For example, let's say in case of a tie, you want to return the country by sorting of it's name.
So, is there will be a tie between CA and US, than the result will be CA:

``````Desired Result 2 =
VAR _current_project = Users[Project]
VAR _tbl_1 =
FILTER(
Users,
Users[Project] = _current_project
)
VAR _tbl_2 =
GROUPBY( _tbl_1,Users[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _top_countries =
FILTER(
_tbl_2,
[@Users] = _max_per_country
)
RETURN
MINX(_top_countries,Users[UsersCountry])``````
Community Champion

Ok, something "quick and dirty" 😄: (US is probably the latest alphabetical country in your data):

``````Desired Result 2 =
VAR _current_project = Users[Project]
VAR _tbl_1 =
FILTER(
Users,
Users[Project] = _current_project
)
VAR _tbl_2 =
GROUPBY( _tbl_1,Users[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _top_countries =
FILTER(
_tbl_2,
[@Users] = _max_per_country
)
RETURN
COALESCE(MAXX(_top_countries,Users[UsersCountry]), "US")``````

Hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

If you like it, give it a thumbs up 🙂

7 REPLIES 7
Community Champion

``````Desired Result =
VAR _current_project = Table[Project]
VAR _tbl_1 =
FILTER(
Table,
Table[Project] = _current_project
)
VAR _tbl_2 =
GROUPBY( _tbl_1,Table[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _desired_country =
CONCATENATEX(
FILTER(
_tbl_2,
[@Users] = _max_per_country
),
Table[UsersCountry], ", "
)
RETURN
_desired_country``````

In case it solved your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

New Member

Great! It does exactly want I wanted.

However, when there is a tie, it shows as example CA,US

How can I pick and show only the first one ?

Community Champion

@Sly I did that on porpuse :))
In case there is a tie, who is the first one? 😅
Give me the logic for that and I'll add a new measure for that but will also keep the previous one for community visabilty. Please don't forget to mark them as a solution

New Member

I want to use that for communications purpose and use the right language for an email.
As example, If I get US,SP I can either use english or Spanish.

But I would prefer to use English because it is a "universal language"

I know I can't handle all the exceptions, but I also know that a majority of people can communicate in English.
Also, I know that I have bunch of users without country set (BLANK). So if I end up with a majority displaying BLANK, I also want to use English by default for this case.

Community Champion

Ok, something "quick and dirty" 😄: (US is probably the latest alphabetical country in your data):

``````Desired Result 2 =
VAR _current_project = Users[Project]
VAR _tbl_1 =
FILTER(
Users,
Users[Project] = _current_project
)
VAR _tbl_2 =
GROUPBY( _tbl_1,Users[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _top_countries =
FILTER(
_tbl_2,
[@Users] = _max_per_country
)
RETURN
COALESCE(MAXX(_top_countries,Users[UsersCountry]), "US")``````

Hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543

If you like it, give it a thumbs up 🙂

New Member

I could use both of your solutions, but I think this one will do the trick !

Thank you very much, really appreciated !

Community Champion

For example, let's say in case of a tie, you want to return the country by sorting of it's name.
So, is there will be a tie between CA and US, than the result will be CA:

``````Desired Result 2 =
VAR _current_project = Users[Project]
VAR _tbl_1 =
FILTER(
Users,
Users[Project] = _current_project
)
VAR _tbl_2 =
GROUPBY( _tbl_1,Users[UsersCountry],"@Users", SUMX(CURRENTGROUP(), 1))
VAR _max_per_country = MAXX(_tbl_2, [@Users])
VAR _top_countries =
FILTER(
_tbl_2,
[@Users] = _max_per_country
)
RETURN
MINX(_top_countries,Users[UsersCountry])``````

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors