Forum Discussion
Returning a value based on a search range
Hi,
I am looking for your help finding out the DAX formula to create the following scenario.
thank you
| Name | Country | New Column | ||
| AA | XX | if AA has XX in country column, then return XX, else others. | ||
| AA | Others | if AA has XX in country column, then return XX, else others. | ||
| AA | Others | if AA has XX in country column, then return XX, else others. | ||
| BB | XX | if BB has XX in country column, then return XX, else others. | ||
| BB | XX | if BB has XX in country column, then return XX, else others. | ||
| BB | Others | if CC has XX in country column, then return XX, else others. | ||
| CC | Others | if CC has XX in country column, then return XX, else others. |
Please see if the following DAX resolves your issue:
Column = CALCULATE ( MAX ( [Country] ), ALLEXCEPT ( Countries, Countries[Name] ) )
7 Replies
Please try the below DAX formula. Please know that I have assumed that you want if there is any country present then we should get the value of country else Others.
Column = IF ( [Country] <> "Others", [Country], "Others" )Please let me know if this is not what you want.
abukapsoun , please mark the post as solution if your issue is resolved. Thanks.
Hi Rajul, but you haven't taken into consideration the Name in the formula?
rajulshah the result is not as expected. I want to get XX, if it exists in the whole range of countries for Name=AA
so for each name, the formula should look into the whole range of country column, and if XX is found at least once, then return XX.
AA might have multiple lines, however in the country column it is either XX or others. So I want to do a kind of search, and if XX is found once, then return XX
Please see if the following DAX resolves your issue:
Column = CALCULATE ( MAX ( [Country] ), ALLEXCEPT ( Countries, Countries[Name] ) )
Is this what you are looking for?
https://1drv.ms/u/s!AiUZ0Ws7G26Rhgtn80K0FSafOSkl?e=aMacBk
