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

[Anonymous]

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

[MartynRamsden · Accepted solution]

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]

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.

[Anonymous]

@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]

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]

@MartynRamsden 

Ah ok, good to know. Thanks again for your help