Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a measure that counts number of working days in the month[Measure 5].
What I want to do is subtract the [Rules] values from this e.g. 23 working days - 2.5 days according to the IF statement in rules calculation
My DAX is:
Measure 7 =
CALCULATE
(
CALCULATE(
countx(
SUMMARIZE(
CROSSJOIN('Employee Absence Full Table','DimDate','Query1'),
'Employee Absence Full Table'[PERSON_ID],'DimDate'[Date],'Query1'[Rules]),'DimDate'[Date]),
FILTER(DimDate,DimDate[WorkingDays] <>BLANK())
)
-COUNTX('Query1','Query1'[Rules])
)
However when I run my Measure 7 DAX I get (23-1) not 2.5:
Is there a way to select the individual values in [Rules] and subtract them from a measure?
Thanks
Measure 7 =
CALCULATE (
COUNTX (
SUMMARIZE (
CROSSJOIN (
'Employee Absence Full Table',
FILTER ( DimDate, DimDate[WorkingDays] <> BLANK () ),
'Query1'
),
'Employee Absence Full Table'[PERSON_ID],
'DimDate'[Date],
'Query1'[Rules]
),
'DimDate'[Date]
),
- COUNTX ( 'Query1', 'Query1'[Rules] )
)
)
Can you please explain the reasoning for using nested CALCULATE and for including the rules table in the Crossjoin?
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |