Forum Discussion
Count values filtering for latest available value
Hi cecep .
Here are the steps you can follow：
1. Create measure.
Measure =
var _max=MAXX(ALLSELECTED(Sheet1),[YEAR])
var _1=SUMMARIZE(FILTER('Sheet1',[YEAR]<=_max),[ANSWER],[COUNTRY])
return
COUNTX(_1,[COUNTRY])2. Result:
When I count in the sample data, the opt-In is also 43. You can check how much the formula shows in your data.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Liu,
Many thank for giving this a try! Actually the issue is that for every "last year" selected I need only one value per country (where one value is available, searching from "last year" to each previous year).
So when I verified this data manually, the count for when 2017 is the last year is 93 individual countries (a number I do manage to confirm on power bi). Which means that 66 and 43 cannot be true as it adds up to 109.
What I need is for the formula to select the latest value and ignore any other values that may exist...
For reference I checked manually the data in book 1 [Sheet 2] and updated it in the folder I shared. Result should be:
OPT IN = 55
OPT OUT = 35
TOTAL = 93
I am amazed at what that 66 and 43 I keep getting correspond to 😂
Your formula I think is the good approach but how do we tell it to ignore other values once it finds one?
Many thanks again,
Best wishes,
Célia.