Except Function Usage

[gagoush]

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 

[v-xjiin-msft]

@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]

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

[gagoush]

Got it.

I supose I need to load it twice?

Thanks,

[v-xjiin-msft]

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

[gagoush]

Do you have a workaround to see the list of users not having... ?

Thanks,

[v-xjiin-msft]

@gagoush

I'm afraid simply DAX expressions cannot achieve your requirement. But you can try to use Query Parameter to filter your table. Create two table visuals and one use euqals parameter to filter, one use does not equals.

Thanks,
Xi Jin.

[v-xjiin-msft]

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