Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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?
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |