Forum Discussion

jeffreyweir's avatar
jeffreyweir
Icon for Helper III rankHelper III
7 years ago
Solved

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

    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
marcorusso's avatar
marcorusso
Icon for Most Valuable Professional rankMost Valuable Professional
7 years ago

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
jeffreyweir's avatar
jeffreyweir
Icon for Helper III rankHelper III
7 years ago

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