cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lazzarjovvch74 Frequent Visitor
Frequent Visitor

Can't compare value of type integer

Hi,

I created the next measure but got an error saying that doesn't support comparing values of type integer with values of type Text.

Measure 1 = IF(HASONEVALUE('MyTable'[Year]), CALCULATE(AVERAGE('MyTable'Price]), 'MyTable'[Year] = FORMAT(VALUES('MyTable'[Year])-1,BLANK())))

Just to emphasize that column Year is of type INTEGER. If I convert the column 'Year' to type Text, the above formula works, but I want to keep it as integer. Any idea how I can rewrite this formula?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Can't compare value of type integer

Silly me,

 

you have to rewrite your measure this way

Measure 1 =  
IF(HASONEVALUE('MyTable'[Year]),
    CALCULATE(
        AVERAGE('MyTable'[Price])
        ,FILTER(
            ALL('MyTable'[Year])
            ,'MyTable'[Year] = MAX('MyTable'[Year])-1
        )
    )
    ,BLANK()
) 

I'm sorry for my confusion.

Regards,
Tom

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
5 REPLIES 5
Super User
Super User

Re: Can't compare value of type integer

Hey,

I'm wondering why you not just use this

... 'MyTable'[Year] = 'MyTable'[Year]-1

Regards,
Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
lazzarjovvch74 Frequent Visitor
Frequent Visitor

Re: Can't compare value of type integer

Hi,

I tried that but an error appeared (red line under 'MyTable'[Year]):

 

Capture.JPG

Super User
Super User

Re: Can't compare value of type integer

Silly me,

 

you have to rewrite your measure this way

Measure 1 =  
IF(HASONEVALUE('MyTable'[Year]),
    CALCULATE(
        AVERAGE('MyTable'[Price])
        ,FILTER(
            ALL('MyTable'[Year])
            ,'MyTable'[Year] = MAX('MyTable'[Year])-1
        )
    )
    ,BLANK()
) 

I'm sorry for my confusion.

Regards,
Tom

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
lazzarjovvch74 Frequent Visitor
Frequent Visitor

Re: Can't compare value of type integer

Tom, absolutely amazing. It works. Please could you explain why did you use 'MAX' function in the FILTER portion of the expression?

 

Best!

Super User
Super User

Re: Can't compare value of type integer

Hey,

I'm glad it works!

Regarding the usage of MAX, please allow me to direct your attention to this blog article I once wrote: https://www.minceddata.info/2018/02/21/the-weird-year-to-date-calculation/

Regards,
Tom
Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!