Forum Discussion

gagoush's avatar
gagoush
Frequent Visitor
8 years ago

Except Function Usage

Hi,

 

I am trying to use Except Function get the minus between two tables.

 

Table 1 : User

Table 2 : CALCULATETABLE(User) Or ALL(User)

 

There is no relationship with these 2 tables. 

 

As the table User get filtered with certain criteria in the report, I am expecting the table EXCEPT(CALCULATETABLE(User),User) to give me the minus of Total Users - User(Filtered)... I also tried EXCEPT(All(User),User).. 

The question is : Does the Except function works with the context filter?

 

Thanks for your help!

 

Gary 

7 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    gagoush

     

    In your scenario, how did you filter the original User table?

     

    Based on my test, it is able to use Except() function to get the minus of Total Users when using Page Level Filters. Please refer:

     

    The source data is like:

     

     

    Create a measure in Table All(User) to calculate the minus of Total Users: 

    Measure = COUNTROWS(EXCEPT('All(User)',User))

    Add a Page Level Filter, and the measure will return the minus of Total Users:

     

     

    Thanks,
    Xi Jin.

    • gagoush's avatar
      gagoush
      Frequent Visitor
      8 years ago

      Thanks Xi it is working fine on my side too with the measure. 

       

      However, I'd like to get the "minus" list of users and my first guess was to create a table ExceptedUsers = EXCEPT(ALL(User);User)

       

      But this won't work. It is always empty.

       

      How come?

       

      Thanks for help again,

       

      Gary

       

       

       

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        gagoush

         

        You should know that the All(User) is a reference or a backup for the table User. When the context in table User is changed, the All(User) table will also be changed automatically. It also means, in fact the two tables are exactly the same.

         

        And for the filters, it is used to filter the table for current page. It will not modify the table itself. Even the filter can modify the context of table User, the All(User) table will also be changed. 

         

        So, in fact there's no EXCEPT between the two tables. That's why you cannot get any result from table ExceptedUsers = EXCEPT(ALL(User),User).

         

        Thanks,
        Xi Jin.