Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

5 REPLIES 5
TomMartens
Super User
Super User

Hey,

I'm wondering why you not just use this

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

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi,

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

 

Capture.JPG

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

Best!

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


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.