Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
---|---|
23 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |