Forum Discussion
problem with a dax formula
Hi,
I have a database like this:
| field1 | field2 | field3 |
| 0,4 | F | 2018 |
| 0,5 | F | 2019 |
| 0,6 | G | 2020 |
where field2 has a text format, field 1 is a number and field3 is like a date but is in text format.
I should make a formula like this one
Measure = IF([mea1]= "labels" OR [mea1]= "chairs", OR [mea1]= "tables",
CALCULATE (
MAX(field1),
field2="F",
field3= MAX(field3)
),
CALCULATE (
MAX(field1)*100 & "%",
field2="F",
field3= MAX(field3)
)
)
I have error at the beginning with the using of or and I have another error after calculate that says that I can't compare value of type number with value of type text, how should I do to remove these errors?
Thank you
Hi Giada_Togliatti ,
Would you please refer to the dax below:
Measure = IF ( [mea1] = "labels" || [mea1] = "chairs" || [mea1] = "tables", CALCULATE ( MAX ( field1 ), FILTER ( table, field2 = "F" && field3 IN VALUES ( field3 ) ) ), CALCULATE ( MAX ( field1 ) * 100 & "%", FILTER ( table, field2 = "F" && field3 IN VALUES ( field3 ) ) ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
9 Replies
What is [mea1]?
mea1 it's the maximum of a text field
- You can use || for OR
Measure = IF([mea1]= "labels" || [mea1]= "chairs" || [mea1]= "tables",
CALCULATE (
MAX(field1),
field2="F",
field3= MAX(field3)
),
CALCULATE (
MAX(field1)*100 & "%",
field2="F",
field3= MAX(field3)
)
)
For the data type I think it's Field3 that's the problem as MAX is converting it to an integer and trying to compare to field3 as text, so you can use CONVERT function to change data type; https://docs.microsoft.com/en-us/dax/convert-function-daxthank you for reply,
I think the problem for field 3 is that it's a text and I compare with the maximum of a text
Measura prova max = CALCULATE(sum(field2), field3= max(field3))
it gives me error,
which formula can I use to have the right result and to covert the field3 in a number?
field 3 is like a date with text format (201806, 201906...)
HI Giada_Togliatti ,
You can't use OR. Replace it with || in the DAX expression as follows:
Measure = IF([mea1]= "labels" || [mea1]= "chairs" || [mea1]= "tables",
CALCULATE (
MAX(field1),
field2="F",
field3= MAX(field3)
),
CALCULATE (
MAX(field1)*100 & "%",
field2="F",
field3= MAX(field3)
)
)Also, there was an extra comma (,) in the 1st line of your dax.
Thanks,
Pragati
Instead of using "MAX(field1)*100 & "%" try use "FORMAT( max('Table'[Column1]),"Percent")"
Hi Giada_Togliatti ,
Would you please refer to the dax below:
Measure = IF ( [mea1] = "labels" || [mea1] = "chairs" || [mea1] = "tables", CALCULATE ( MAX ( field1 ), FILTER ( table, field2 = "F" && field3 IN VALUES ( field3 ) ) ), CALCULATE ( MAX ( field1 ) * 100 & "%", FILTER ( table, field2 = "F" && field3 IN VALUES ( field3 ) ) ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
v-deddai1-msft thank you for the formula, but it doesn't work, there is always the problem with the max
Hi Giada_Togliatti ,
I have modified my original reply, please try again.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
