Forum Discussion
DAX VAR Function
Very difficult to troubleshoot without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
That being said, I would change your return to test the various parts of your formula, such as:
TEST =
VAR CentreHeadcount = CALCULATE(COUNT('Centre Employees'[Employee ID]),'Calendar'[Month Offset]>0)
VAR CentreTerminations = CALCULATE(DISTINCTCOUNT('Centre Terminations'[Employee ID]),'Calendar'[Month Offset]>0)
return
VAR Terminations12MTD = CALCULATE(CentreTerminations,DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-12, MONTH),'Calendar'[Month Offset]>0)
VAR Headcount12MTD = CALCULATE(AVERAGEX(ALLSELECTED('Calendar'),CentreHeadcount),DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-12, MONTH),'Calendar'[Month Offset]>0)
return
Terminations12MTD
//CALCULATE(IF(DIVIDE(Terminations12MTD,Headcount12MTD)=BLANK(),0,DIVIDE(Terminations12MTD,Headcount12MTD)),'Calendar'[Month Offset]>0)
So, see if that value is BLANK and if it is that would explain why you are getting 0% back
- JP89916 years agoKudo Commander
Hi Greg_Deckler
Thanks for your response, sorry for the late reply I have been away this weekend.
I have created a dummy file which should be of use.
My aim is to reduce the Centre Turnover KPI to just one Measure instead of having five.
Anonymous no I am not fully aware of ALLSELECTED and its limitations so more than happy for that to be adjusted.