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 ...
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.

    • gagoush's avatar
      gagoush
      Frequent Visitor
      8 years ago

      Got it.

       

      I supose I need to load it twice?

       

      Thanks,

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

        gagoush

         

        No, I don't think load it twice will work.

         

        The original data for the two tables are same, the filters (no matter what kind of filter in Power BI) will not change the original data in source table. It only affects the visual.

         

        Thereby, the Except() function will not work for the two exactly same tables.

         

        Thanks,
        Xi Jin.