Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX comparison operations do not support comparing values of type True/False with values of type Int

Hi All, 

 

This might be a simple solution but i am trying to avoid creating multiple columns 

 

I am trying to convert some figures from different frequencies within a date range however i am getting the error : 

 

DAX comparison operations do not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

 

So far i currently have this within my formula

 

Weekly Service Charge = IF(UnitCharge[ChargeFrequencyRef] = "M" & DATESBETWEEN(UnitCharge[StartDate],01/04/2018,01/04/2019) & UnitCharge[ChargeTypeId] = 2,UnitCharge[ChargeAmount]*12/52,
IF(UnitCharge[ChargeFrequencyRef] = "Q"& DATESBETWEEN(UnitCharge[StartDate],01/04/2018,01/04/2019) & UnitCharge[ChargeTypeId] = 2,UnitCharge[ChargeAmount]*4/52,
IF(UnitCharge[ChargeFrequencyRef] = "Y" & DATESBETWEEN(UnitCharge[StartDate],01/04/2018,01/04/2019) & UnitCharge[ChargeTypeId] = 2 ,UnitCharge[ChargeAmount]/52,
IF(UnitCharge[ChargeFrequencyRef] = "W" & DATESBETWEEN(UnitCharge[StartDate],01/04/2018,01/04/2019) & UnitCharge[ChargeTypeId] = 2,UnitCharge[ChargeAmount]))))
 
Can anyone advise where i am going wrong?
 
Many thanks
Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Hi Anonymous 

     

    You need to use double ampersand '&&' between your logical arguments. A single ampersand simply tries to concatenate text.

    I'd also recommend using the SWITCH function in place of your nested IF statements as it makes the code easier to read.

     

    Weekly Service Charge =
SWITCH (
    TRUE (),
    UnitCharge[ChargeFrequencyRef] = "M"
        && DATESBETWEEN ( UnitCharge[StartDate], 01 / 04 / 2018, 01 / 04 / 2019 )
        && UnitCharge[ChargeTypeId] = 2, UnitCharge[ChargeAmount] * 12 / 52,
    UnitCharge[ChargeFrequencyRef] = "Q"
        && DATESBETWEEN ( UnitCharge[StartDate], 01 / 04 / 2018, 01 / 04 / 2019 )
        && UnitCharge[ChargeTypeId] = 2, UnitCharge[ChargeAmount] * 4 / 52,
    UnitCharge[ChargeFrequencyRef] = "Y"
        && DATESBETWEEN ( UnitCharge[StartDate], 01 / 04 / 2018, 01 / 04 / 2019 )
        && UnitCharge[ChargeTypeId] = 2, UnitCharge[ChargeAmount] / 52,
    UnitCharge[ChargeFrequencyRef] = "W"
        && DATESBETWEEN ( UnitCharge[StartDate], 01 / 04 / 2018, 01 / 04 / 2019 )
        && UnitCharge[ChargeTypeId] = 2, UnitCharge[ChargeAmount]
)

     

    Best regards,

    Martyn

     

    If I answered your question, please help others by accepting it as a solution.

5 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi Anonymous 

     

    You need to use double ampersand '&&' between your logical arguments. A single ampersand simply tries to concatenate text.

    I'd also recommend using the SWITCH function in place of your nested IF statements as it makes the code easier to read.

     

    Weekly Service Charge =
SWITCH (
    TRUE (),
    UnitCharge[ChargeFrequencyRef] = "M"
        && DATESBETWEEN ( UnitCharge[StartDate], 01 / 04 / 2018, 01 / 04 / 2019 )
        && UnitCharge[ChargeTypeId] = 2, UnitCharge[ChargeAmount] * 12 / 52,
    UnitCharge[ChargeFrequencyRef] = "Q"
        && DATESBETWEEN ( UnitCharge[StartDate], 01 / 04 / 2018, 01 / 04 / 2019 )
        && UnitCharge[ChargeTypeId] = 2, UnitCharge[ChargeAmount] * 4 / 52,
    UnitCharge[ChargeFrequencyRef] = "Y"
        && DATESBETWEEN ( UnitCharge[StartDate], 01 / 04 / 2018, 01 / 04 / 2019 )
        && UnitCharge[ChargeTypeId] = 2, UnitCharge[ChargeAmount] / 52,
    UnitCharge[ChargeFrequencyRef] = "W"
        && DATESBETWEEN ( UnitCharge[StartDate], 01 / 04 / 2018, 01 / 04 / 2019 )
        && UnitCharge[ChargeTypeId] = 2, UnitCharge[ChargeAmount]
)

     

    Best regards,

    Martyn

     

    If I answered your question, please help others by accepting it as a solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      MartynRamsden  Thanks for this. 

       

      Sorry i should have remembered! Im so used to Excel so still trying to get used to the DAX forumlas . 

       

      Thanks again

      • MartynRamsden's avatar
        MartynRamsden
        Icon for Solution Sage rankSolution Sage
        6 years ago

        No worries!

         

        You'll probably find that your DATESBETWEEN functions don't work as you expect to either.

        If you want to hard code the dates into the expression, you'll need to use the DATE function.

         

        E.g.

        DATESBETWEEN ( UnitCharge[StartDate], DATE ( 2018, 04, 01 ), DATE ( 2019, 04, 01 ) )

         

        Otherwise, you'll need to use an aggregation which returns a single value (e.g MIN or MAX)

        DATESBETWEEN ( UnitCharge[StartDate], MIN ( UnitCharge[StartDate] ), MAX ( UnitCharge[StartDate] ) )

         

        Best regards,

        Martyn

         

        If I answered your question, please help others by accepting it as a solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello MartynRamsden .

       

      I've been trying to create a conditional column and am facing the same error.

      The formula should be something like this (taken from Tableu):

      IF [Case Number]= [Case Number (Cases w Article Attached 2020+)] THEN 'Cases with Article attached' ELSE

      'Cases without article attached' END

       

      My power bi formula:

      Cases with Article =
      IF('Tech Case Report'[Case Number]<> ISBLANK('Tech Case Report'[Cases With Article attached Report.Case Number]), "Cases with Article Attached",

      IF('Tech Case Report'[Case Number]= ISBLANK('Tech Case Report'[Cases With Article attached Report.Case Number]), "Cases without Article"))

      Please advise. 
      Thanking you.