Forum Discussion

_Amy_'s avatar
_Amy_
Icon for Helper I rankHelper I
4 years ago
Solved

DAX comparison operations

I have a DAX formula that calculates the number of employees who have departed in the last 12 months and it works well.

I am just trying to add to the formula an additional filter where the 'departure flag' equals 1 but it is giving me this error: 
'DAX compariosn operations do not support comparing values of type True/False with values of type Integer.' 

Here is the DAX formula, with the additional part that is giving me the error I have added in red

 

Departed past 12 months =

 

VAR selectedDate = LASTDATE('Date Table'[Date])

VAR selectedDate12MonthsBefore = NEXTDAY(SAMEPERIODLASTYEAR(selectedDate))

 

RETURN

CALCULATE(

DISTINCTCOUNT('All Employees'[Full Name]),

FILTER(

ADDCOLUMNS(

'All Employees',

"Departed Past 12 months", IF([Termination Date]>=selectedDate12MonthsBefore && [Termination Date]<=selectedDate,1,0) && 'All Employees'[Departure Flag] = 1) ,[Departed Past 12 months] = 1))

 

I have tried to use the VALUE and FORMAT functions to change the Departure Flag and I have also changed it between text, whole number and 'any' in Power Query and I still get the same error every time.

Please help!

Need Help
  • _Amy_'s avatar
    _Amy_
    4 years ago

    Thank you, I changed it around so the part of the formula that was causing the issue is now at the start of the formula and it works:

    "Departed Past 12 months", IF  ('All Employees'[Departure Flag] = 1 && [Termination Date]>=selectedDate12MonthsBefore && [Termination Date]<=selectedDate,1,0)) ,[Departed Past 12 months] = 1))

4 Replies

  • MahyarTF's avatar
    MahyarTF
    Icon for Memorable Member rankMemorable Member
    4 years ago

    You put the condition without checking it.

    In my opinion, you need to put your condition in If Function against the out of it

    • _Amy_'s avatar
      _Amy_
      Icon for Helper I rankHelper I
      4 years ago

      Thank you, I changed it around so the part of the formula that was causing the issue is now at the start of the formula and it works:

      "Departed Past 12 months", IF  ('All Employees'[Departure Flag] = 1 && [Termination Date]>=selectedDate12MonthsBefore && [Termination Date]<=selectedDate,1,0)) ,[Departed Past 12 months] = 1))
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    _Amy_ , Try like

     

    "Departed Past 12 months", IF([Termination Date]>=selectedDate12MonthsBefore && [Termination Date]<=selectedDate && 'All Employees'[Departure Flag] = 1 && [Departed Past 12 months] = 1,1,0)

    • _Amy_'s avatar
      _Amy_
      Icon for Helper I rankHelper I
      4 years ago

      Thank you, I tried that but it didn't work.

      The "[Departed Past 12 months]" is the name of the measure I am creating here so I can't use it in the formula like that.