cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jeffreyweir Member
Member

CALCULATETABLE(ALLEXCEPT ditches column completely, instead of just ignoring filters on it

Howdy folks. I'm using TREATAS to propogate a filter between two tables where a relationship can't exist.

 

I'm trying to propogate an Ethnicity filter from a disconnected slicer onto a fact table. At the same time, I need to clear the filters on that fact table (it is filtered by region). 

 

I can't just do this:

 

CALCULATETABLE( 
        ALL(CTE)
        ,TREATAS( 
            VALUES('Ethnicity Matrix'[Code])
            ,CTE[EthnicCode]
        )
    )

...because  that ALL gets applied last, and then wipes out the filtering I just applied using TREATAS

 

So I thought I just needed to use ALLEXCEPT like this:

 

CALCULATETABLE( 
        ALLEXCEPT(CTE,CTE[EthnicCode])
        ,TREATAS( 
            VALUES('Ethnicity Matrix'[Code])
            ,CTE[EthnicCode]
        )
    )

...in order to tell the DAX to leave my newly-applied EthnicCode filter alone.

 

But what happens is that it actually ditches the EthnicCode column from the Table entirely!

 

CalcTable 2.jpg

 

I just don't understand why it would do this. ALLEXCEPT is supposed to return a table with all filters removed except for the filters on the specified columns. It's not supposed to ditch those specified columns entirely.

 

Can anyone explain what's going on here?

1 ACCEPTED SOLUTION

Accepted Solutions
marcorusso Member
Member

Re: CALCULATETABLE(ALLEXCEPT ditches column completely, instead of just ignoring filters on it

You are creating a CROSSJOIN that cannot be solved in the storage engine. GENERATE is useless in your code because you are not performing any context transition. Use of SUMMARIZE to compute aggregations is not a best practice. You can use TREATAS as a filter. Look at this equivalent code.

=
VAR CTE_EthnicCodeFilter =
    TREATAS (
        VALUES ( 'Ethnicity Matrix'[Code] ),
        CTE[EthnicCode]
    )
VAR YearToNowRegionTerminated =
    CALCULATETABLE (
        SUMMARIZE (
            CTE,
            'Calendar'[Year To Now],
            CTE[Physical_Region]
        ),
        ALL ( CTE ),
        CTE_EthnicCodeFilter,
        CTE[EmpStatus] = "Terminated",
        CTE[EmpType] = "Perm",
        CTE[Termination_Type] = "Unplanned",
        NOT ISBLANK ( CTE[Physical_Region] )
    )
VAR LeftTable =
    ADDCOLUMNS (
        YearToNowRegionTerminated,
        "Left", - CALCULATE (
            SUM ( CTE[EmpCount] )
        )
    )
VAR MonthsRegionsNotTerminated =
    CALCULATETABLE (
        SUMMARIZE (
            CTE,
            CTE[MonthStart],
            CTE[Physical_Region]
        ),
        CTE_EthnicCodeFilter,
        CTE[EmpStatus] <> "Terminated",
        CTE[EmpType] = "Perm"
    )
VAR MonthsRegionsNotTerminated_WithTotal =
    ADDCOLUMNS (
        MonthsRegionsNotTerminated,
        "Total", CALCULATE (
            SUM ( CTE[EmpCount] )
        )
    )
VAR RigthTable =
    SELECTCOLUMNS (
        MonthsRegionsNotTerminated_WithTotal,
        "YearToNow", [MonthStart],
        "Region", [Physical_Region],
        "Total", [Total]
    )
VAR CrossJoinTables =
    CROSSJOIN (
        LeftTable,
        RigthTable
    )
VAR JoinTables =
    FILTER (
        CrossJoinTables,
        [YearToNow] = [Year To Now]
            && [Physical_Region] = [Region]
    )
VAR TurnoverTable =
    SELECTCOLUMNS (
        JoinTables,
        "Left", [Left],
        "Total", [Total],
        "TurnoverCalc", [Left] / ( [Total] ),
        "Region", [Region],
        "Date", [YearToNow]
    )
VAR GetMaxTurnover =
    MAXX (
        TurnoverTable,
        [TurnoverCalc]
    )
RETURN
    GetMaxTurnover

View solution in original post

4 REPLIES 4
marcorusso Member
Member

Re: CALCULATETABLE(ALLEXCEPT ditches column completely, instead of just ignoring filters on it

ALLEXCEPT is mainly a filter modifier, even though you can use it as a table function, that use is uncommon because of the expanded table.

You can write the code this way in a calculated table where you don't have an initial filter context:

 

 

CALCULATETABLE (
    CTE,
    TREATAS (
        VALUES ( 'Ethnicity Matrix'[Code] ),
        CTE[EthnicCode]
    )
)

But it's not dynamic. If you want to use it within a measure (which is a scalar value, so you cannot use it as a return value) you could write this:

CALCULATETABLE (
    CTE,
    ALL ( CTE ),
    TREATAS (
        VALUES ( 'Ethnicity Matrix'[Code] ),
        CTE[EthnicCode]
    )
)

But it's not clear to me why you want to use this construct - the entire CALCULATETABLE should be the filter argument of another CALCULATE if you use it in a measure.

 

 

 

jeffreyweir Member
Member

Re: CALCULATETABLE(ALLEXCEPT ditches column completely, instead of just ignoring filters on it

Hi Marco.

This is part of a much larger DAX pattern. 

The use-case is that I am trying to dynamically generate the maximum possible value of staff turnover across a whole bunch of different visuals (one for each site) so I can use the resulting value to force a bunch of visuals to 'adopt' the exact same scale. But to do this I need to work out the maximum possible value that Turnover could take across each different site. 

 

Here's an example when this scaling is turned off: each visual's axis is autoscaled, but because the quantums are different you can't directly compare the size of the bars:

 

Unscaled.jpg

 

...but if I toggle my DAX scaling to TRUE, the 5 bottom graphs are forced to adopt the same Axis, meaning you can directly compare the height of the bars. (The top graph is for all sites, and so I don't scale that one):

 

Scaled.jpg

 

 

 The DAX in it's entirety looks like this:

 

 

MAXX(
    SELECTCOLUMNS(
        FILTER(
            GENERATE(
                SUMMARIZE(
                    FILTER(
                        ALL(CTE)
                       , CTE[EthnicCode] IN VALUES('Ethnicity Matrix'[Code])
                        &&CTE[EmpStatus]="Terminated"
                        &&CTE[EmpType] = "Perm"
                        && CTE[Termination_Type]="Unplanned"
                        && CTE[Physical_Region] <> BLANK()),
                    'Calendar'[Year To Now],
                    CTE[Physical_Region],
                    "Left",
                    -SUM(CTE[EmpCount])
                    ), 
                    SELECTCOLUMNS(
                        SUMMARIZE(
                            FILTER(
                                ALL(CTE)
                                , CTE[EthnicCode] IN VALUES('Ethnicity Matrix'[Code])
                                &&CTE[EmpStatus]<>"Terminated"
                                &&CTE[EmpType] = "Perm"),
                            CTE[MonthStart],
                            CTE[Physical_Region],
                            "Total",
                            SUM(CTE[EmpCount])
                        ),
                        "YearToNow", [MonthStart],
                        "Region", [Physical_Region],
                        "Total", [Total]
                        
                        )
                    ),
                [YearToNow] = [Year To Now] && [Physical_Region] = [Region]),
            "Left", [Left],
            "Total", [Total],
            "TurnoverCalc", [Left]/([Total])
            ,"Region", [Region], "Date", [YearToNow]
            )
    ,[TurnoverCalc])

I was thinking that TREATAS would probably  be faster, but it just didn't seem to work when used as the <filter> argument of the FILTER function. 

 

I'll try your suggestion re using CALCULATETABLE with (All(CTE) as part of the CALCULATETABLE <filter> argument...I can't believe I didn't think of that already!

marcorusso Member
Member

Re: CALCULATETABLE(ALLEXCEPT ditches column completely, instead of just ignoring filters on it

You are creating a CROSSJOIN that cannot be solved in the storage engine. GENERATE is useless in your code because you are not performing any context transition. Use of SUMMARIZE to compute aggregations is not a best practice. You can use TREATAS as a filter. Look at this equivalent code.

=
VAR CTE_EthnicCodeFilter =
    TREATAS (
        VALUES ( 'Ethnicity Matrix'[Code] ),
        CTE[EthnicCode]
    )
VAR YearToNowRegionTerminated =
    CALCULATETABLE (
        SUMMARIZE (
            CTE,
            'Calendar'[Year To Now],
            CTE[Physical_Region]
        ),
        ALL ( CTE ),
        CTE_EthnicCodeFilter,
        CTE[EmpStatus] = "Terminated",
        CTE[EmpType] = "Perm",
        CTE[Termination_Type] = "Unplanned",
        NOT ISBLANK ( CTE[Physical_Region] )
    )
VAR LeftTable =
    ADDCOLUMNS (
        YearToNowRegionTerminated,
        "Left", - CALCULATE (
            SUM ( CTE[EmpCount] )
        )
    )
VAR MonthsRegionsNotTerminated =
    CALCULATETABLE (
        SUMMARIZE (
            CTE,
            CTE[MonthStart],
            CTE[Physical_Region]
        ),
        CTE_EthnicCodeFilter,
        CTE[EmpStatus] <> "Terminated",
        CTE[EmpType] = "Perm"
    )
VAR MonthsRegionsNotTerminated_WithTotal =
    ADDCOLUMNS (
        MonthsRegionsNotTerminated,
        "Total", CALCULATE (
            SUM ( CTE[EmpCount] )
        )
    )
VAR RigthTable =
    SELECTCOLUMNS (
        MonthsRegionsNotTerminated_WithTotal,
        "YearToNow", [MonthStart],
        "Region", [Physical_Region],
        "Total", [Total]
    )
VAR CrossJoinTables =
    CROSSJOIN (
        LeftTable,
        RigthTable
    )
VAR JoinTables =
    FILTER (
        CrossJoinTables,
        [YearToNow] = [Year To Now]
            && [Physical_Region] = [Region]
    )
VAR TurnoverTable =
    SELECTCOLUMNS (
        JoinTables,
        "Left", [Left],
        "Total", [Total],
        "TurnoverCalc", [Left] / ( [Total] ),
        "Region", [Region],
        "Date", [YearToNow]
    )
VAR GetMaxTurnover =
    MAXX (
        TurnoverTable,
        [TurnoverCalc]
    )
RETURN
    GetMaxTurnover

View solution in original post

jeffreyweir Member
Member

Re: CALCULATETABLE(ALLEXCEPT ditches column completely, instead of just ignoring filters on it

Hi Marco. With some minor modifications, your approach works great! I had to combine the Var YearToNowRegionTerminated and LeftTable into one step, in order to correctly summarise EmpCount within the appropriate filter context.  Ditto with the MonthsRegionsNotTerminated and RightTable variables.

 

This is a great lesson to me on refactoring code using variables, and I'm thrilled you took the time to steer me in the right direction.

 

I ordered your DAX and SSAS books recently and they've just arrived, so no doubt both will help me arrive at more elegant/efficient solutions like this. (And I have also recently signed up to your SSAS training but haven't started yet). And I'll be sharing my experiences with the books and training via the Wellington PowerBI User Group I co-run with your fellow MVP Phil Seamark. 

 

Here's the amended code.

 

 

 

Axis TO = 
VAR CTE_EthnicCodeFilter =
    TREATAS (
        VALUES ( 'Ethnicity Matrix'[Code] ),
        CTE[EthnicCode]
    )
    
VAR LeftTable =
    CALCULATETABLE (
        SUMMARIZE (
            CTE,
            'Calendar'[Year To Now],
            CTE[Physical_Region],
        	"Left", -SUM(CTE[EmpCount])
        ),
        ALL ( CTE ),
        CTE_EthnicCodeFilter,
        CTE[EmpStatus] = "Terminated",
        CTE[EmpType] = "Perm",
        CTE[Termination_Type] = "Unplanned",
        NOT ISBLANK ( CTE[Physical_Region] )
    )
    
    
VAR MonthsRegionsNotTerminated =
    CALCULATETABLE (
        SUMMARIZE (
            CTE,
            CTE[MonthStart],
            CTE[Physical_Region],
            "Total", SUM(CTE[EmpCount])
        ),
        ALL ( CTE ),
        CTE_EthnicCodeFilter,
        CTE[EmpStatus] <> "Terminated",
        CTE[EmpType] = "Perm"
    )

    
VAR RightTable =
    SELECTCOLUMNS (
        MonthsRegionsNotTerminated,
        "YearToNow", [MonthStart],
        "Region", [Physical_Region],
        "Total", [Total]
    )
VAR CrossJoinTables =
    CROSSJOIN (
        LeftTable,
        RightTable
    )
VAR JoinTables =
    FILTER (
        CrossJoinTables,
        [YearToNow] = [Year To Now]
            && [Physical_Region] = [Region]
    )
VAR TurnoverTable =
    SELECTCOLUMNS (
        JoinTables,
        "Left", [Left],
        "Total", [Total],
        "TurnoverCalc", [Left] / ( [Total] ),
        "Region", [Region],
        "Date", [YearToNow]
    )
VAR GetMaxTurnover =
    MAXX (
        TurnoverTable,
        [TurnoverCalc]
    )
RETURN
    GetMaxTurnover

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 762 guests
Please welcome our newest community members: