create a column that is based on the distinct count of text value

[Sut_Datanaut]

Example below

I need to create a column that is based on 2 columns. The first column is country, which can only be US or UK. The second column is a name, which is linked to the country column. If there is only one distinct name, then there will be only either US or UK, but if there are two names which are duplicates, then they are in both US and UK. I need the third column to show that is there is just one name, then populate the country, but if there are two names, then one ot the rows show "both" and the other column is a blank.

Thanks is advanced

Name	Country	Type
Sam	US	US
Jared	UK	Both
Jared	US
John	UK	UK
Josh	US	US

[v-lili6-msft]

hi, @Sut_Datanaut 

For your case, but if there are two names, then one ot the rows show "both" and the other column is a blank,

You could use this way as below:

Step1:

Add an index column in edit queries

Step2:

Use this formula to create a new column

Result = 
VAR _distincount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Country] ),
        FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
    )
RETURN
    VAR _first =
        CALCULATE (
            MIN ( 'Table'[Index] ),
            FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
        )
    RETURN
        IF (
            _distincount = 1,
            'Table'[Country],
            IF ( 'Table'[Index] = _first, "Both", BLANK () )
        )

Result:

and here is sample pbix file, please try it.

Best Regards,

Lin

[Sut_Datanaut]

Hi,

Thank you for the reply! If i wanted to have "both" in both of the rows, how would i go about changing the syntax?

Thanks for the help. I will mark as accepted solution.

[Ashish_Mathur]

Hi,

This calculated column formula works

=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])))=1,Data[Country],"Both")

Hope this helps.

[v-lili6-msft · Accepted solution]

hi, @Sut_Datanaut 

Just adjust the formula as below:

Result 2 = 
VAR _distincount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Country] ),
        FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
    )
RETURN

        IF (
            _distincount = 1,
            'Table'[Country],
            "Both"
        )

By the way, for this case(If i wanted to have "both" in both of the rows), you do not need to add an index column in edit queries.

Result:

Best Regards,

Lin

[Sut_Datanaut]

Hi,

Worked great! Thank you!

[Sut_Datanaut]

Hi, @v-lili6-msft

Thank you for the reply! If i wanted to have "both" in both of the rows, how would i go about changing the syntax?

Thanks for the help. I will mark as accepted solution.

[Anonymous]

@v-lili6-msft -

I'm very curious how you were able to include an attachment to your post.  I can see how I can post photos and links to videos but no way to simply attach a PBIX.  Is this only availablt to v-msft accounts?

Thanks!

Eric

[Cmcmahan]

It's a feature available to v-msft accounts and Super Users, @Anonymous