Forum Discussion

Helper 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

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

Most Valuable Professional

7 years ago

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

Helper III

7 years ago

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:

...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):

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

Most Valuable Professional

7 years ago

=
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

Helper 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

Forum Discussion

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

Related content

Refresh completed with warnings

Refresh completed with warnings

Update completed with notifications.

ValueFormatter ignoring culture

Make measure ignore specific filter