Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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:
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |