Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add column according to values from different rows

I created this simplified table of my data:

 

 CountryValueYearResult
France23002021Both
Spain54322021Only '21
Germany02021Only '22
Italy02021None
France31232022Both
Spain02022Only '21
Germany44512022Only '22
Italy02022None

 

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.

Need Help
  • zzcc's avatar
    zzcc
    5 years ago

    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!

1 Reply

  • zzcc's avatar
    zzcc
    Frequent Visitor
    5 years ago

    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!