Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |