Returning a value based on a search range

[abukapsoun]

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.

[Ahmedx]

Is this what you are looking for?
https://1drv.ms/u/s!AiUZ0Ws7G26Rhgtn80K0FSafOSkl?e=aMacBk

[abukapsoun]

Hi Rajul, but you haven't taken into consideration the Name in the formula?

[abukapsoun]

@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

[rajulshah · Accepted solution]

@abukapsoun ,

Please see if the following DAX resolves your issue:

Column =
CALCULATE ( MAX ( [Country] ), ALLEXCEPT ( Countries, Countries[Name] ) )

[rajulshah]

@abukapsoun ,

Oh okay, let me just work it that way then.

[rajulshah]

@abukapsoun ,

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.

[rajulshah]

@abukapsoun , please mark the post as solution if your issue is resolved. Thanks.