Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Sly
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
SpartaBI
Community Champion
Community Champion

@Sly 

 

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 🙂

View solution in original post

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])

View solution in original post

SpartaBI
Community Champion
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 🙂

View solution in original post

7 REPLIES 7
SpartaBI
Community Champion
Community Champion

@Sly 

 

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 🙂

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 ?

SpartaBI
Community Champion
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

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.

SpartaBI
Community Champion
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 🙂

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

Thank you very much, really appreciated !

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])

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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