Forum Discussion
How to maintain explicit filters when using ALL functions
Hi all
I am trying to count the values on a column, based on one of my extarnal filters but getting rid of that same external filter. See my example.
Country Project ID
France 34
France 56
France 34
EEUU 21
EEUU 34
EEUU 67
EEUU 43
Spain 23
Spain 15
This is my table, my external filter is "Project ID = 23" which belongs to Spain and this is the only filter which the report consumer will see. I want my Dax formula to show how many projects are there in Spain (looking for answer = 2) based on that only filter.
I have tried
CALCULATE(COUNTROWS(Table);ALLEXCEPT(Table;Table[Country])) but because the Country filter is not in the report it just counts all the rows in the table.
Thanks a lot in advance.
hi Anonymous
Just try this simple logic to get it:
Measure = var _country=VALUES('Table'[Country]) return CALCULATE(COUNTA('Table'[Country]),FILTER(ALL('Table'), 'Table'[Country] in _country))Result:
and here is sample pbix file, please try it.
Regards,
Lin
8 Replies
- vanessafvg
Community Champion
when you say external filter, how are you filtering currently?
- amitchandak
Super User
Anonymous , Try like
CALCULATE(COUNTROWS(Table);filter(all(Table);Table[Country]="Spain"))
Not sure I got it correctly
- vanessafvg
i think you might need to use selected value to filer, does this article help?
http://dataap.org/blog/2018/07/22/capture-the-selected-filter-in-powerbi-dax/
Hi vanessafvg
Looks like my Anti-Virus (ESET) is blocking me from accessing that Link, could you post here a possible answer?
Thanks
v-lili6-msftCommunity Support
hi Anonymous
Just try this simple logic to get it:
Measure = var _country=VALUES('Table'[Country]) return CALCULATE(COUNTA('Table'[Country]),FILTER(ALL('Table'), 'Table'[Country] in _country))Result:
and here is sample pbix file, please try it.
Regards,
Lin
