Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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 Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 error 

SUM 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,'