Remove all but one filter context from variable table

[flyinggnugget]

Hi,

I am trying to work on a measure that creates a table variable and then calculate the distinct rows of that table variable. I am trying to remove all filter context from that table except for one specific colum. Would like to seek help on this. This is a simplified explanation.

Currently what I've tried is something like this:

measure = 
VAR temp_table = CALCULATETABLE( ALL('Table1'), ALLSELECTED('Table1'[Country]))

RETURN COUNTROWS( DISTINCT (SELECTCOLUMNS(temp_table,"value",[Country])))

but the results seems to also ignore the filter context for country when I play around with the country slicer.

I have also tried the following:

measure = 
VAR temp_table = ALL('Table1'))

RETURN CALCULATE( COUNTROWS( DISTINCT (SELECTCOLUMNS(temp_table,"value",[Country]))), ALLSELECTED('Table1'[Country]))

but the results were the same as above.

Greatly appreciate some help on this. Thanks!

[Anonymous]

Hi, @flyinggnugget 

You can try using the function, which retains the filters on the specified columns while removing the filters on all other columns:

measure = 
VAR temp_table = 
    CALCULATETABLE(
        ALLEXCEPT('Table1', 'Table1'[Country]),
        ALLSELECTED('Table1'[Country])
    )
RETURN 
    COUNTROWS( DISTINCT (SELECTCOLUMNS(temp_table, "value", [Country])))

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.