cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JP8991
Helper I
Helper I

DAX VAR Function

Hi All,

 

I am trying to get a VAR formula to work to avoid having multiple measures to calculate Turnover.

Currently I have five measures to calculate Turnover (see below), I am hoping to reduce that to one with VAR.

 

  • Centre Headcount
  • Centre Headcount 12MTD Average
  • Centre Terminations
  • Centre Terminations 12MTD
  • Centre Turnover

Invidually they all work however when I put them in one formula I get 0%.

 

This is how I have done my test Measure:

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

CALCULATE(IF(DIVIDE(Terminations12MTD,Headcount12MTD)=BLANK(),0,DIVIDE(Terminations12MTD,Headcount12MTD)),'Calendar'[Month Offset]>0)

 

As mentioned each of these steps work on their own however once put into one equation they don't.

5 REPLIES 5
JP8991
Helper I
Helper I

@Greg_Deckler  @Anonymous 

 

Any suggestions as to how I can improve these Measures?

 

My aim is to have them reduced from 5 to 1 and refrain from using ALLSELECTED if that isn't advisable, I provided a sample file in an earlier post.

Anonymous
Not applicable

Hi there. Please read this about ALLSELECTED: https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

It'll give you an idea about the function and why it should be used wisely.

Best
D
Anonymous
Not applicable

Hi there.

 

I'd like to first kindly ask you: Do not create monster formulas. It's not fun, I assure you, especially for those who will have to maintain such code. Also, please format your measures when you post them on the forum. Please respect your readers' time. Thanks.

 

Now I'll tell you why your monster does not work. It's because variables to which you've assigned values are STATIC. They cannot be changed. Here's your measure formatted (www.daxformatter.com😞

 

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
    )
VAR Terminations12MTD =
    CALCULATE (
        CentreTerminations, -- STATIC VALUE!!!
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -12, MONTH ),
        'Calendar'[Month Offset] > 0
    )
VAR Headcount12MTD =
    CALCULATE (
        AVERAGEX ( ALLSELECTED ( 'Calendar' ), CentreHeadcount ), -- = CENTREHEADCOUNT always or BLANK
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -12, MONTH ),
        'Calendar'[Month Offset] > 0
    )
RETURN
    CALCULATE (
        IF (
            DIVIDE ( Terminations12MTD, Headcount12MTD ) = BLANK (),
            0,
            DIVIDE ( Terminations12MTD, Headcount12MTD )
        ),
        'Calendar'[Month Offset] > 0
    )

Also, there's no need to have multiple RETURNs in there. This only obscures the code.

 

By the way, ALLSELECTED is a very complex function. The most complex function in whole DAX. Do you fully understand what it does? Do you know what shadow context is? I'll give you a hint that will save your life: Please never use in your code something the functionality of which you don't fully understand. If you do use it, you'll be having countless bugs, many of which you'll not be even aware of.

 

Best

D

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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.

 

Turnover Test 

 

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.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors