Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |