Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everybody.
Just finished reading the Definitive Guide to DAX therefore, I feel rather humilated posing this question.
I think it's a super easy one but I'm just out of time with that report thus, thought of asking.
A table with time off records. Each [User] has several time off records.
Each time off record has an [End] date for the last date of the leave and an [Available] number of remaining accrued hours.
There are certain records that have no [Available] number (records of unpaid leave that do not affect the accrued hours)
The table also contains other data such as [Manager] and [Department].
I would like a measure that will show the latest [Available] record per employee.
It will have to filter out the records with no [Available] data.
It needs to be an average because once collapsing any additional fields, we will need the average available hours per, say, [Manager] and/or [Department].
Currently this is where I stand, it works well in Employee granualarity but when collapsing to Manager, the average makes no sense.
=VAR tablStart=CALCULATETABLE(
'Time Off',NOT(
ISBLANK(
'Time Off'[Available]
)
),
LASTDATE(
'Time Off'[End]
)
)
VAR tabl=CALCULATETABLE(
tablStart,ALLEXCEPT(
'Time Off','Time Off'[Manager Name]
)
)
VAR LatestRemain=CALCULATE(
AVERAGEX(
tabl,'Time Off'[Available]
)
)
RETURN
IF(
LatestRemain=0,BLANK(),IF(
LatestRemain<0,MROUND(
LatestRemain,-0.5
),MROUND(
LatestRemain,0.5
)
)
)
My bets of naughtiness for ALLEXCEPT...
What do you think?
Many thanks in advance
Solved! Go to Solution.
Done!
measure 1:=VAR tablFirst=SUMMARIZE('Time Off','Time Off'[Person])
VAR tabl=ADDCOLUMNS(tablFirst,"lastAvailable", CALCULATE(SUM('Time Off'[Available]),LASTDATE('Time Off'[End])))
VAR LatestRemain=CALCULATE(AVERAGEX(tabl, [lastAvailable]))
RETURN
IF(LatestRemain=0,BLANK(),IF(LatestRemain<0,MROUND(LatestRemain,-0.5),MROUND(LatestRemain,0.5)))
Done!
measure 1:=VAR tablFirst=SUMMARIZE('Time Off','Time Off'[Person])
VAR tabl=ADDCOLUMNS(tablFirst,"lastAvailable", CALCULATE(SUM('Time Off'[Available]),LASTDATE('Time Off'[End])))
VAR LatestRemain=CALCULATE(AVERAGEX(tabl, [lastAvailable]))
RETURN
IF(LatestRemain=0,BLANK(),IF(LatestRemain<0,MROUND(LatestRemain,-0.5),MROUND(LatestRemain,0.5)))
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |