Forum Discussion

flyinggnugget's avatar
flyinggnugget
Frequent Visitor
2 years ago

Remove all but one filter context from variable table

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!

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.