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
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 |
Solved! Go to 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
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
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.
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
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
96 | |
87 | |
68 |
User | Count |
---|---|
169 | |
133 | |
130 | |
103 | |
95 |