Hi there,
I am trying to convert a dashboard from import to direct query mode and realized a calculated column created is not translating easily due to limitation of DAX functions with Direct Query mode. Specifically, I keep getting the error that "Calculate it not a function", "Countrows is not a function", and "Datesbetween" is not a function.
Anyone know another function that will work the same in direct query? This was the original dax to calculate hours which takes into account work day hours....
The reason I need this as a calculated column is I need it as a value for a line chart where I am visualizing putaway hours by date.
PTWY Hrs =
var shiftstart = 6 //shift start at 6am
var shiftend = 17 //shift end at 5pm
var wrkdyhours = shiftend-shiftstart //11 workday hours
var wrkdyafterhours = 24-(shiftend-shiftstart) //13 after shift hours on 11 hour workday
return
if('INBD Items'[Putaway Date].[Date]='INBD Items'[GR Date].[Date],//if putaway done on same day as gr
DATEDIFF('INBD Items'[GR Date],'INBD Items'[Putaway Date],MINUTE)/60,//then calculate the minutes
(DATEDIFF('INBD Items'[GR Date],'INBD Items'[Putaway Date],MINUTE)-(//else calculate minutes and subtract non working hours
CALCULATE(countrows('Date'),DATESBETWEEN('Date'[Date],'INBD Items'[GR Date],'INBD Items'[Putaway Date]),'Date'[IsWorkingDay]=FALSE())*24*60+ //calculate non working day convert to minutes to subtract
(CALCULATE(countrows('Date'),DATESBETWEEN('Date'[Date],'INBD Items'[GR Date],'INBD Items'[Putaway Date]),'Date'[IsWorkingDay]=TRUE())-1)*wrkdyafterhours*60 //calculate after hour minutes to subtract
))/60//convert to hours
)
