Add column according to values from different rows

[Anonymous]

I created this simplified table of my data:

Country	Value	Year	Result
France	2300	2021	Both
Spain	5432	2021	Only '21
Germany	0	2021	Only '22
Italy	0	2021	None
France	3123	2022	Both
Spain	0	2022	Only '21
Germany	4451	2022	Only '22
Italy	0	2022	None

Basically, I want to obtain the column Result from the first three columns using the following logic:

- If a Country reports values greater than 0 in any row for the year 2021 AND year 2022, result would be Both.

- If the values greater than 0 are reported for only one year, result would be Only '21 or Only '22, depending on the case.

- If a Country only reports 0 in all rows, result would be None.

Note that as in the example, the result must be shown in all rows for each country.

I'm oppen to any suggestions.

Thanks in advance.

[zzcc · Accepted solution]

Hi, @Anonymous 

try to create a measure like this:

Measure = 
var _t=FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country]))
var _t2=FILTER(_t,[Value]>0)
var _t3=
SWITCH(COUNTROWS(_t2),2,"Both",1,"Only"&MAXX(_t2,[Year]),"None")
return _t3

result:

Best Regards

zc

Did I answer your question? Mark my post as a solution!