Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
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.
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.
@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])
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |