Forum Discussion
ALLEXCEPT not returning values as expected
Hi
I have come across a (for me) strange result using allexcept:
Using a table (Table1):
| Date | Value |
| 01.01.2018 | Test1 |
| 02.01.2018 | Test |
| 03.01.2018 | Test |
| 04.01.2018 | Test1 |
| 05.01.2018 | Test2 |
| 06.01.2018 | Test1 |
| 07.01.2018 | Test |
| 08.01.2018 | Test |
| 09.01.2018 | Test |
| 10.01.2018 | Test |
and the measure
CountTest = CALCULATE(COUNT(Table1[Date]);ALLEXCEPT(Table1;Table1[Date]))
If I have a slicer with Value set to Test and a Date slicer between 02.01.2018 and 10.01.2018 I would expect CountTest to give 9 but instead it gives 6 which is the number of dates that has Test as Value. If the slicer of Date is not set it shows the (for me) expected result of 10 even if Value is filtered to Test.
I can create a workaround using:
CALCULATE(COUNT(Table1[Date]);FILTER(ALL(Table1);AND(Table1[Date]>=min(Table1[Date]);Table1[Date]<=max(Table1[Date]))))
but that will not work if a user wants to filter a non-continous date range.
Do any of you have a solution for this and do you know if this is the wanted behaviour of Allexcept?
The data provided is only an example and the real problem is a bit more complex but a solution to the basic problem will most likely also be the solution to my main problem.
Any help is appreciated.
Magnus
Hi MagSve,
Based on my test, we can take the following steps to meet your requirement.
1. Create a date table.
Table = CALENDARAUTO()
2. Update your measure as below.
CountTest = CALCULATE(COUNTROWS(Table1),ALLEXCEPT('Table','Table'[Date]))3. Then we can get the excepted result.
For more details, please check the pbix as attached.
As we know, ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns. However, in your scenes the context should be a new table after filtered by date as the picture below. So you cannot get the expected result.
Regards,
Frank
2 Replies
Hi MagSve,
Based on my test, we can take the following steps to meet your requirement.
1. Create a date table.
Table = CALENDARAUTO()
2. Update your measure as below.
CountTest = CALCULATE(COUNTROWS(Table1),ALLEXCEPT('Table','Table'[Date]))3. Then we can get the excepted result.
For more details, please check the pbix as attached.
As we know, ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns. However, in your scenes the context should be a new table after filtered by date as the picture below. So you cannot get the expected result.
Regards,
Frank
Thank you for the solution.
I guess my thoughs about allexcept was a bit wrong as I thought it would remove all other filters than a direct filter on the date-column, but as the slicer on the Value-column also filters out some dates this is also counted as a context filter on date as well, correct?
