How to maintain explicit filters when using ALL functions

[Anonymous]

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.

[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/

[Anonymous]

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-msft · Accepted solution]

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

[Anonymous]

Thank you @v-lili6-msft

This worked correctly;-)

[amitchandak]

@Anonymous , Try like

CALCULATE(COUNTROWS(Table);filter(all(Table);Table[Country]="Spain"))

Not sure I got it correctly

[Anonymous]

Hi amitchandak

That won't work, as I the consumer could choose another project form another country, like EEUU and in that case, I want my formula to count how many projects are in EEUU.

Thanks 

[vanessafvg]

when you say external filter, how are you filtering currently?

[Anonymous]

Hi Vanessa

I have just the "Project ID" filter in my report, in the example, the consumer is selecting the Project ID 23 in an Slicer. I want that report consumer to see the number of projects in Spain as 23 belongs to Spain. Hope I am clear enough;-)

Thanks a lot