ALLEXCEPT - where only applied to a single column

[EWBWEBB]

Hi There 

I have a fairly messy piece of DAX to calculate a moving annual average (MAA) for absence. 

I can't use the normal time intelligence functions as my business operates in 4 weekly periods, which are not all exact lengths of time or the same each year - believe me if I could change this I would.

However, onto the issue. 

The below calculation works but I really only need the 'ALL' to apply to two columns out of a large table. The two columns being PeriodIndex & PeriodYear.

The way I have managed to get this working though is to use ALLEXCEPT and then everytime someone asks for a new filtered MAA I just add the relevant column.

Any ideas on how I can just have the ALL applied to the two prviously mentioned columns.

This is the DAX - but the calculation is the average of the current and prior 12 periods (13 in total). Which we have to calculate by adding the 13 periods together then dividing by 13, we can't simply take an average of the 13 periods. 

MaaAbsence = 

VAR _P =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index] >= MAX ( STAFFPERIODIC[period index] )
                && STAFFPERIODIC[period index] <= MAX ( STAFFPERIODIC[period index] )
        )
    )
VAR _P1 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 1
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 1
        )
    )
VAR _P2 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 2
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 2
        )
    )
VAR _P3 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 3
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 3
        )
    )
VAR _P4 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 4
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 4
        )
    )
VAR _P5 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 5
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 5
        )
    )
VAR _P6 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 6
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 6
        )
    )
VAR _P7 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 7
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 7
        )
    )
VAR _P8 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 8
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 8
        )
    )
VAR _P9 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 9
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 9
        )
    )
VAR _P10 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 10
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 10
        )
    )
VAR _P11 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 11
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 11
        )
    )
VAR _P12 =
    CALCULATE (
        [*percent sickness],
        FILTER (
            ALLEXCEPT (
                STAFFPERIODIC,
                STAFFPERIODIC[DEPARTMENT],
                STAFFPERIODIC[DIVISION],
                STAFFPERIODIC[AREA],
                STAFFPERIODIC[DESCRIPTION],
                STAFFPERIODIC[ACTIVITY],
                STAFFPERIODIC[COST_CODE]
            ),
            STAFFPERIODIC[period index]
                >= MAX ( STAFFPERIODIC[period index] ) - 12
                && STAFFPERIODIC[period index]
                    <= MAX ( STAFFPERIODIC[period index] ) - 12
        )
    )
VAR Total = _P + _P1 + _P2 + _P3 + _P4 + _P5 + _P6 + _P7 + _P8 + _P9 + _P10 + _P11 + _P12
VAR Results =
    DIVIDE ( Total, 13, 0 )
RETURN
Results 

[AntrikshSharma]

@EWBWEBB I don't get it why can't you just use ALL?

[EWBWEBB]

I did initially but when using all it removed all the filters for department, division and so on.

I need it so that a user can select the respective department and then the MAA is shown for that department. When I put this together the ALL was taking away ALL the filters and meaning I couldn't do that?

[daXtreme]

@EWBWEBB 

This is not only messy. It's a maintenance nightmare. I'd never write code like this, especially that you are repeating chunks of code and this, as we all know, is the highway to programming hell. There are better ways to tackle such issues and the best way is to have a good model, according to Best Practices.

Socond is this page: www.daxpatterns.com.

[EWBWEBB]

@daXtreme 

I don't disagree that it is a nightmare but at the moment I have to work with what I have got.

I have a data mart in the pipeline but it is month's away and in the short term resolving some messy code to make my life easier is the simpler option to learning SQL to a good enough level to rebuild the data warehouse. 

Bit of a one man band and learning as we go at the moment.

[daXtreme]

You're missing the point, I guess. Unless you're forced to use DQ, you can massage your data to whatever standard you need in Power Query (it better be a good standard). I'd suggest you do that for your own good and sanity; you don't have to be SQL guru to do that. Apart from that, you should immediatly extract the commonalities between the variables into one structure in order to avoid duplication. Later on, you should re-write the code in an efficient and terse manner.

[EWBWEBB]

@daXtreme - Thank you for your reply. 

Could you give an example of how you might extract the commonalities?

I'll look into the rest but at this point I'm not convinced it is worth the effort of figuring all that out with a revised model on the way in, in a few months.

[daXtreme · Accepted solution]

This is what good code should look like:

MaaAbsence =
var Commonality1 =
    ALLEXCEPT(
        STAFFPERIODIC,
        STAFFPERIODIC[DEPARTMENT],
        STAFFPERIODIC[DIVISION],
        STAFFPERIODIC[AREA],
        STAFFPERIODIC[DESCRIPTION],
        STAFFPERIODIC[ACTIVITY],
        STAFFPERIODIC[COST_CODE]
    )
var Commonality2 = MAX( STAFFPERIODIC[period index] )
var Commonality3 = 12
var TotalPs =
    SUMX(
        SELECTCOLUMNS(
            GENERATESERIES(0, Commonality3),
            "@Back", [Value]
        ),
        var CurrentBackValue = [@Back]
        var Output =
            CALCULATE(
                [*percent sickness],
                FILTER(
                    Commonality1,
                    STAFFPERIODIC[period index] = Commonality2 - CurrentBackValue
                )
            )
        return
            Output
    )
VAR Result =
    DIVIDE( TotalPs, Commonality3 + 1, 0 )
RETURN
    Result

[EWBWEBB]

@daXtreme - thank you so much - I'll give this a bash.

[daXtreme]

It's your choice. I've been there many a time and know that you have to put an effort into it RIGHT NOW, not later. Later will be too late and you'll be struggling much, much more than now, to the point where you'll be re-writing. But it's your choice 🙂

Commonalities should be extracted into variables.

[EWBWEBB]

@daXtreme - thanks