topic Re: DAX ALL() not working in DAX Commands and Tips

DAX ALL() not working

Sureshot — Fri, 16 Aug 2019 13:20:43 GMT

Hi.

I have a Matrix with 4 columns as follows:

Line Of Business - Current Year - % of Total - % Division

Liability - $47,993,349 - 63% - 63%

Property - $27,765,694 - 36% - 36%

Umbrella - $358,047 - 0% - 0%

Total - $76,117,090 - 100% - 100%

When I filter by a specific agent I get:

Line Of Business - Current Year - % of Total - % Division

Liability - $661,413 - 49% - 1%

Property - $695,683 - 51% - 1%

Umbrella - $2,826 - 0% - 0%

Total - $1,359,922 - 100% - 2%

The FACT Table is the source for the Current Year value.

The FACT table is filtered by an Agent Dimension and both are joined on AgentCode.

I need the % Division field to not change when the Agent Filter is applied (in fact when any dimension filter is applied).

Here is my DAX but the ALL() does not ignore the filters.

% Division = DIVIDE(SUM('Agency Scorecard 3a'[Business DWP]), CALCULATE(SUM('Agency Scorecard 3a'[Business DWP]), ALL()),0)

Can anyone help?

Re: DAX ALL() not working

Stachu — Sat, 17 Aug 2019 04:19:11 GMT

ALL works exxectly as designed here - it does remove all the filters, including the one you put on the specific agent

it basically does

661,413/7,611,709 ~= 1% 
agent specific sales divided by total sales

rather than what you want

661,413/1,359,922 ~=49%
agent specific sales divided by agent specific total sales

you may want to use ALLSELECTED or ALLEXCEPT instead

also you can have a read here
https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

Re: DAX ALL() not working

Sureshot — Mon, 19 Aug 2019 15:13:34 GMT

Hi.

Thanks for your response but what I want is for % Division to stay at 63% & 36% regardless of what filter is applied, in this case agent.

Is there a way to ignore all filters or specify what filters to ignore in order to satisfy my requirement?

I've tried ALL, ALLSELECTED and ALLEXCEPT

Thanks.

Re: DAX ALL() not working

Stachu — Mon, 19 Aug 2019 15:36:16 GMT

Right now you only remove filters for the denominator, for nominator you want to remove all filters except for the Line of Business

e.g. like this

% Division =
VAR __Nominator =
    CALCULATE (
        SUM ( 'Agency Scorecard 3a'[Business DWP] ),
        ALLEXCEPT ( 'Agency Scorecard 3a', 'Agency Scorecard 3a'[Line Of Business] )
    )
VAR __Denominator =
    CALCULATE ( SUM ( 'Agency Scorecard 3a'[Business DWP] ), ALL () )
RETURN
    DIVIDE ( __Nominator, __Denominator, 0 )

you may need to adjust the table name for the nominator

Re: DAX ALL() not working

Sureshot — Mon, 19 Aug 2019 15:41:56 GMT

That worked - you're a genius!