New table with prioritised results in column

[bullius]

Hi

I would like a table SUMMARIZED by Employee ID, showing their main country of operation.

My data looks something like this:

Employee ID	Country
1	UK
1	France
2	UK
2	Spain
3	UK
4	Germany
5	Brazil

I want a table that SUMMARIZES this by Employee ID and shows one country per Employee ID, prioritising non-UK countries for those that have more than one country of operation.

E.g.

Employee ID	Country
1	France
2	Spain
3	UK
4	Germany
5	Brazil

Any ideas about how I might do this?

Thanks!

[Vvelarde · Accepted solution]

@bullius

Hi, A way to solve this:

In Original Table:

Create 3 columns:

isUK =
COUNTROWS (
    FILTER (
        Table1,
        Table1[Country] = "UK"
            && Table1[Employee ID] = EARLIER ( Table1[Employee ID] )
    )
)

TotalRows =
COUNTROWS (
    FILTER ( Table1, Table1[Employee ID] = EARLIER ( Table1[Employee ID] ) )
)

inNew =
IF (
    Table1[isUK] = 1
        && Table1[TotalRows] = 2
        && Table1[Country] = "UK";
    "No";
    "Yes"
)

or Just 1 Column:

InNew =
VAR isUK =
    COUNTROWS (
        FILTER (
            Table1,
            Table1[Country] = "UK"
                && Table1[Employee ID] = EARLIER ( Table1[Employee ID] )
        )
    )
VAR TotalRows =
    COUNTROWS (
        FILTER ( Table1, Table1[Employee ID] = EARLIER ( Table1[Employee ID] ) )
    )
RETURN
    IF (
        Table1[isUK] = 1
            && Table1[TotalRows] = 2
            && Table1[Country] = "UK",
        "No",
        "Yes"
    )

And A new Summarized Table:

Table =
SUMMARIZE (
    FILTER ( Table1; Table1[inNew] = "Yes" ),
    Table1[Employee ID],
    Table1[Country]
)