DAX Comparison Operations do not support

[Sharma0815]

I am trying to create a measure to calculate the percentage of the most recent month's positive feedbacks from my dataset and to get that I'm using the below measure.

Pre Month Survey positive =

VAR Prev_month =
    MAX ( Sheet1[record date].[Month] )
VAR Current_year =
    MAX ( Sheet1[record date].[Year] )
VAR pre_mont_top2 =
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( Sheet1[ID] ),
            FILTER (
                Sheet1,
                Sheet1[feedback ] = "positive"
                    && VALUE ( Sheet1[record date].[Month] ) = Prev_month
                    && VALUE ( Sheet1[record date].[Year] ) = Current_year
            )
        ),
        CALCULATE (
            DISTINCTCOUNT ( Sheet1[ID] ),
            FILTER (
                Sheet1,
                VALUE ( Sheet1[record date].[Month] ) = Prev_month
                    && VALUE ( Sheet1[record date].[Year] ) = Current_year
            )
        )
    )
RETURN
    pre_mont_top2

I'm not understanding why it's returning the "DAX Comparison Operations do not support comparing values of type number with values of type text  " error

[v-robertq-msft · Accepted solution]

Hi, @Sharma0815 

According to your description, I can understand clearly what you want to get, you want to get the percentage of the most recent month's positive feedbacks, right? I also created some data and used DAX to achieve this, you can try my steps:

This is my test data based on your picture:

1. I created a calculated column:

datediff =

DATEDIFF([record date],TODAY(),DAY)

2. I created a measure:

Percent =
var _leastdatadiff=
CALCULATE(MIN('Table'[datediff]),FILTER(ALL('Table'),[feedback]="Positive"))
var _mostrecentmonth=
MONTH(CALCULATE(MAX('Table'[record date]),FILTER(ALL('Table'),[datediff]=_leastdatadiff&&[feedback]="positive")))
var _mostrecentyear=
YEAR(CALCULATE(MAX('Table'[record date]),FILTER(ALL('Table'),[datediff]=_leastdatadiff&&[feedback]="positive")))
var _countofpositive=
COUNTX(FILTER(ALL('Table'),MONTH([record date])=_mostrecentmonth&&YEAR([record date])=_mostrecentyear&&[feedback]="positive"),[ID])
var _countofall=
COUNTX(FILTER(ALL('Table'),MONTH([record date])=_mostrecentmonth&&YEAR([record date])=_mostrecentyear),[ID])
return
DIVIDE(_countofpositive,_countofall)

3. I created a card chart and placed the measure:

And I think the result can be what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result so that we can help you in advance.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Sharma0815 ,

I do not recommend using the DateType specific extensions like so [datevalue].Month as I consider them not being consistent as this

[2021-02-21].Month returns a "February" whereas this

[2021-02-21].Year returns 2021 and the datatype is "whole number", something we might expect.

The conditional expression VALUE( [datecolumn].Month = Prev_Month in your statement is responsible for the error you are facing. The DAX engine check is as follows (simplified):

Hmm, I know VALUE( ... ) will return a number (the left hand side of the conditional expression) but the right hand side is a text, for this I will return an error as I do not execute implicit type converson (basically this is a good thing).

Instead I'm recommending using the DAX functions MONTH( '...'[datevalue] ) and YEAR(...) as both return the same data type.

Hopefully, this explains why you are facing the error message.
Use YEAR(...) and MONTH(...), instead of '...'[...].Month and '...'[...].Year

Regards,

Tom