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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Sut_Datanaut
Helper II
Helper II

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

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

 

NameCountryType
SamUSUS
JaredUKBoth
JaredUS 
JohnUKUK
JoshUSUS
1 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:

4.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-lili6-msft
Community Support
Community Support

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

1.JPG

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:

2.JPG

 

and here is sample pbix file, please try it.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Hi,

This calculated column formula works

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

4.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Worked great! Thank you!

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
Not applicable

@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

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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