Keeping and ignoring filtercontext over different tables in one measure

[OlafCdeWit]

Hi all,

Our customer would like to see - in the tooltip, so secundary information - the totaal number of employees for the selected year for a selected level of the organisation (it's a hierarchie of three levels).

The data model looks like this, where the relationship between the two facts is inactive. I realise this is not an optimal data model, unfortunetaly we have to deal with this right now.

My goal is to get a sum of the 'EMPCOUNT'. To do this, I believe I need a measure that keeps the filters on the highlighted columns, but ignores the filters on everything else.

So far, the closest I got was this:

sum_empcount = 
CALCULATE(
    SUM(FACTEMPCOUNT[EMPCOUNT])
    ,USERELATIONSHIP(
        FACTTICKET[FACTEMPCOUNTKEY]
        ,FACTEMPCOUNT[FACTEMPCOUNTKEY]
    )
    ,ALLEXCEPT(
        FACTTICKET
            ,FACTTICKET[DIMDATEKEY]
            ,FACTTICKET[DEPARTMENT_L1]
            ,FACTTICKET[DEPARTMENT_L2]
            ,FACTTICKET[DEPARTMENT_L3]
    )
    ,REMOVEFILTERS(DIMEMPLOYEE)
    ,REMOVEFILTERS(DIMTRAIN)
)

But this measure still ignores the filtering on DIMDATE, so I get a sum over all the years instead of the selected one.

What is the right DAX expression for this problem?

[tamerj1]

Hi @OlafCdeWit 
Please try

sum_empcount =
CALCULATE (
    SUM ( FACTEMPCOUNT[EMPCOUNT] ),
    USERELATIONSHIP ( FACTTICKET[FACTEMPCOUNTKEY], FACTEMPCOUNT[FACTEMPCOUNTKEY] ),
    REMOVEFILTERS ( DIMEMPLOYEE ),
    REMOVEFILTERS ( DIMTRAIN ),
    REMOVEFILTERS ( FACTTICKET ),
    VALUES ( FACTTICKET[DIMDATEKEY] ),
    VALUES ( FACTTICKET[DEPARTMENT_L1] ),
    VALUES ( FACTTICKET[DEPARTMENT_L2] ),
    VALUES ( FACTTICKET[DEPARTMENT_L3] )
)

[OlafCdeWit]

Unfortunately this doesn't work. The DIMDATE filters correctly now, but the DIMTRAIN, DIMEMPLOYEE and and other FACTTICKET columns also still filter the output.

[tamerj1]

Hi @OlafCdeWit 
I really don't understand what could be the reason. I mean you have mentioned that using your code you only had the problem that DDATE is not filtering the result. That was the only change I did (replaceing ALLEXCEPT with REOVEFILTERS + VALUES). The rest of the code was kept the same. Unless it was not working in the first place?