Forum Discussion
Filtering with ALLEXCEPT()
Hi there,
I'm struggling to get a Measure to act the way it should using AllExcept().
For context , imagine my data is:
| Business | Cat | Project | Cost |
| Bus A | X | P1 | $1 |
| Bus A | Y | P2 | $5 |
| Bus A | Y | P3 | $3 |
| Bus B | X | P4 | $8 |
| Bus B | Y | P5 | $7 |
I have a slicer for Business + Project.
On the dashboard, I want to display the total cost for the catergory of the project across the whole business.
E.g. if Project 3 is selected, then the measure will return $8 (5+3). If Project 1 is selected it will be $1. If Project 5 is selected it will be $7 etc.
Initially, to create this measure I duplicated the query and added the filters into the table before bringing it into PowerBI. I called this table TEST.
The measure I created, which does work, is
SUM TEST =
CALCULATE(
DISTINCTCOUNT(
TEST[Project]
),
ALLEXCEPT(
TEST,
TEST[Business],
TEST[Cat]
)
)
This works as expected.
In my report I have a few other filters in place across the page that needs to also be considered. This is because the project will appear a few times in the dataset, but we only want to look at one where the Source = "Main", Progress = "Active" and the date is after 01/09/2020.
I know wanted to repeat this measure with the real table (called 'Table'). This of course, didn't work properly as it wasn't considering the additional filters required of the report.
But even when adding in the filters, it isn't working. It actually appears to be over filtering the data as the number is significantly smaller than the real one.
The measure looks like this
SUM 2 REAL =
CALCULATE(
DISTINCTCOUNT(
Table[Project]
),
ALLEXCEPT(
Table,
Table[Business],
Table[Cat],
Table[Source],
Table[Progress],
Table[Date]
)
)
Any clues as to why this isn't working as expected?
No combination of the additional filters is working. I feel this should be simple!
Hi Anonymous ,
According to your statement, I know that your calculate logic to get sum cost based on all values in same Business and Category as the Project you select. As far as I know, ALLEXCEPT will keep the filter of the columns in it. But we only select Project not Business or Category. So, I think you won't get the result you want.
Your basic measure should look like as below.
SUM TEST = VAR _SELECTBUSINESS = SELECTEDVALUE ( 'Table'[Business] ) VAR _SELECTCATEGORY = SELECTEDVALUE ( 'Table'[Cat] ) RETURN CALCULATE ( SUM ( 'Table'[Cost] ), FILTER ( ALL ( 'Table' ), 'Table'[Business] = _SELECTBUSINESS && 'Table'[Cat] = _SELECTCATEGORY ) )Then you want to keep the filter of [Source], [Date], [Progress], so add them into filter and change ALL to ALLEXCEPT.
SUM TEST = VAR _SELECTBUSINESS = SELECTEDVALUE ( 'Table'[Business] ) VAR _SELECTCATEGORY = SELECTEDVALUE ( 'Table'[Cat] ) RETURN CALCULATE ( SUM ( 'Table'[Cost] ), FILTER ( ALLEXCEPT ( 'Table', Table[Source], Table[Progress], Table[Date] ), 'Table'[Business] = _SELECTBUSINESS && 'Table'[Cat] = _SELECTCATEGORY ) )Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , allexpect will honor those filter from slicer/filter and from row context too ,
You can try like
SUM TEST =
CALCULATE(
DISTINCTCOUNT(
TEST[Project]
),
filter(allselected(TEST)
TEST,
TEST[Business] = max(TEST[Business]) &&
TEST[Cat] = max(TEST[Cat])
)
)Hi there,
That is just returning the value for the one project. Not the sum across the catergory for all projetcs in that business.
Also, I am getting an errorSUM TEST =
CALCULATE(
DISTINCTCOUNT(
TEST[Project]
),
filter(allselected(TEST)TEST,
TEST[Business] = max(TEST[Business]) &&
TEST[Cat] = max(TEST[Cat])
)
)I had to remove this section 'TEST,'
Hi Anonymous ,
According to your statement, I know that your calculate logic to get sum cost based on all values in same Business and Category as the Project you select. As far as I know, ALLEXCEPT will keep the filter of the columns in it. But we only select Project not Business or Category. So, I think you won't get the result you want.
Your basic measure should look like as below.
SUM TEST = VAR _SELECTBUSINESS = SELECTEDVALUE ( 'Table'[Business] ) VAR _SELECTCATEGORY = SELECTEDVALUE ( 'Table'[Cat] ) RETURN CALCULATE ( SUM ( 'Table'[Cost] ), FILTER ( ALL ( 'Table' ), 'Table'[Business] = _SELECTBUSINESS && 'Table'[Cat] = _SELECTCATEGORY ) )Then you want to keep the filter of [Source], [Date], [Progress], so add them into filter and change ALL to ALLEXCEPT.
SUM TEST = VAR _SELECTBUSINESS = SELECTEDVALUE ( 'Table'[Business] ) VAR _SELECTCATEGORY = SELECTEDVALUE ( 'Table'[Cat] ) RETURN CALCULATE ( SUM ( 'Table'[Cost] ), FILTER ( ALLEXCEPT ( 'Table', Table[Source], Table[Progress], Table[Date] ), 'Table'[Business] = _SELECTBUSINESS && 'Table'[Cat] = _SELECTCATEGORY ) )Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
