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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
OlafCdeWit
Frequent Visitor

Keeping and ignoring filtercontext over different tables in one measure

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.

OlafCdeWit_0-1700227770473.png

 

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?

3 REPLIES 3
tamerj1
Super User
Super User

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] )
)

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

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors