Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jeffreyweir
Helper III
Helper III

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

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

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.

 

 

 

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!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.