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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Also, why does the ALLEXCEPT in my test table not honour the slicer that is linked to that table, but the one that is linked to the real table effects it?

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.