problem with a dax formula

[Giada_Togliatti]

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

[v-deddai1-msft · Accepted solution]

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

[Giada_Togliatti]

@v-deddai1-msft thank you for the formula, but it doesn't work, there is always the problem with the max

[v-deddai1-msft]

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

[FarhanAhmed]

Instead of using "MAX(field1)*100 & "%" try use "FORMAT( max('Table'[Column1]),"Percent")"

[Pragati11]

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

[AllisonKennedy]

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-dax

[Giada_Togliatti]

thank 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...)

[FarhanAhmed]

What is [mea1]?

[Giada_Togliatti]

mea1 it's the maximum of a text field