Forum Discussion
lookup user
Hi,
I have a country table that joins to a user table.
| Country | ID |
| Australia | 1 |
| US | 2 |
| Japan | 3 |
| User | Country ID |
| User 1 | 1 |
| User 2 | 1 |
| User 1 | 2 |
| User 2 | 3 |
| User 1 | 3 |
In the user table, a user can belong to multiple countries.
User 1 can belong to the US, Australia, Japan. But I need to overwrite this, if the user belongs to Australia, I need all the 3 records to be updated of that user to be updated to Australia.
Can I create a new column with this rule? The rule should pick up the users belonging to Australia, and assign Australia in the new column.
PBIfanatic thanks for the clarification, add a new column using the following DAX expression, change the column name as per your model, assuming both tables
New Country = VAR __IsAustralia = CALCULATE ( MIN ( CC[Country ID] ), ALLEXCEPT ( CC, CC[User] ), Country[Country] = "Australia" ) RETURN IF ( NOT ISBLANK ( __IsAustralia ), __AustraliaID, CC[Country ID] )✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
are already related
8 Replies
Hi PBIfanatic ,
Create a new column:
Country ID 2 = IF(CALCULATE(MIN(User[Country ID]),ALLEXCEPT(User,User[User]))=1,1,User[Country ID])JoriIf I answered your question, please mark it as a solution to help other members find it more quickly.
Connect on LinkedinPBIfanatic thanks for the clarification, add a new column using the following DAX expression, change the column name as per your model, assuming both tables
New Country = VAR __IsAustralia = CALCULATE ( MIN ( CC[Country ID] ), ALLEXCEPT ( CC, CC[User] ), Country[Country] = "Australia" ) RETURN IF ( NOT ISBLANK ( __IsAustralia ), __AustraliaID, CC[Country ID] )✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
are already related
PBIfanatic why it will be Australia not the US for user 1, what is the logic which country to pick if user belongs to two countries?
Hi parry2k , if a user belongs to Australia AND any other countries, then the user needs to be assigned under Australia for reporting reasons. That is the rule. I have a similar one for UK, France, Belgium where the user needs to be marked under UK if the user appears in UK, France/Belgium.
PBIfanatic Ok but what happens if a user belongs to both US and Japan, nothing changes in that case? Is this condition only for Australia? I don't like to provide the solution without fully understanding the problem, like others who jump into the solution without understanding the full scope of the question.
Hi parry2k , No rules to the scenario you mentioned, if a user belonged to US and Japan, it remains as is.
This is only applicable to the countries I mentioned.
Hi,
Please try the following:
If the rows in the User column are the same then the logic is returning the minimum value of of CountryID. If the minimum value is 1, then the result is 1, if not then the result is CountryID. I then added a second custom column that does a lookup to the country table and returns the Country name based on CountryID.
Please let me know if thi helps.
-JID
