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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gustav_Juel_Hal
New Member

Get initials from one column, compare it to another one with initials and then return the name

Hi guys,

 

I am having a problem with trying to search for country initials, take those initials and and return the countries full name.

 

I have tried something like the following, where the ISOCODE describes the CountryName with initials in the Countries table, and tries to compare the initials with the OtherRepresentedCountries from the Missions table and then return the Country name:

 

_searchFunction =
var _toCountryName =
IF(
SELECTEDVALUE('Missions'[UMHR_OtherRepresentedCountries] ) == SELECTEDVALUE(Countries[ISOCode]),
SELECTEDVALUE('Countries'[CountryName], BLANK())
)
RETURN

_toCountryName
 
All in all I just want to take the initials from the 'Missions'[UMR_OtherRepresentedCountrie], search for the initials in the ISOCODE, and return whatever countryname fits that ISOCODE.
 
Hope you can help me.
7 REPLIES 7
Gustav_Juel_Hal
New Member

Thank you for the fast answer.

 

When I try to create a relationship between UMHR_OtherRepresentedCountries and ISOCode, I get the error message: "You can't create relationship between these two columns, because one of these columns must have unique values. "

 

Is there a way that i can just compare the initial characters like a string.

E.g. if the two columns both have "PL" as initial characters, then it returns the country with those initials, which would be Poland I guess. So that I get a column which follows the initials of UMHR_OtherRepresentedCountries.

Text lookups and relationships will all need one value - it doesn't work like Excel which will just return the FIRST value. Power BI needs to know which value. So if there are two PL values in the lookup table it will return Blank or error with my method. 

 

Are you able to share the data?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

The data is unfortunately confidential. 

 

But if you have two lists and both contain "PL" isn't it possible with and IF statement to return true and if true then return the country that corresponds to those initials. 

As long as the lookup list only contains PL once. You could also do a Merge in Power Query, but again you really need unique country code first. 

https://excelwithallison.blogspot.com/search?q=merge

 

Can you provide sample of the data and why it has duplicates? Or rather how the duplicates are set up? 

 

As per my blog linked above, if we have Key = 1 three times in the lookup table, I don't know whether to return good, poor or average, so with the DAX lookup functions it will return the alternate result since there's more than one choice. 

 

Why are the values not unique and how can we help Power BI pick the one row it needs? I need more info on your dataset please. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

There are already a relationship between Missions and Countries with an ID. Maybe that can be useful

In the OtherRepresentedCountries column there can be a lot of Transactions coming from PL, whereas in ISOCode all of the initials are just once/unique. ISOCode is in a row with the countryName, an ID and so on.  So if i can search through the OtherRepresentedCountries and compare the initial characters with the ISOCode. Then when there is a match, it returns countryName in the same row as the ISOCode by using the ID of the row.

AllisonKennedy
Super User
Super User

@Gustav_Juel_Hal is there only one value of Country Name for each ISOCODE? Make sure ISOCODE is unique, then:

 

You can create a relationship between Missions table and Countries table using [UMR_OtherRepresentedCountrie] and IsoCode columns? Then you might not need the column, but if you want you can add a new column to the Missions table:

 

Country Column = RELATED(Countries[CountryName])


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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