Another "operations do not support comparing values of type true/false with values of type text"

[BrianPansy]

I'm trying to count the number of times a measure is true across N amount of vendors:

% Compliant = 

DIVIDE(SUM('STP_HEDIS_CUR'[COMPLIANT_CNT]),SUM('STP_HEDIS_CUR'[ELIGIBLE_CNT]))

Pretty straighfoward, which yields this table:

Next I'm trying to count the number of times that measure is true (e.g. shaded green) but I'm getting the message in the title of the post. I tried the below formula:

VendorCount = 
   
CALCULATE (
        COUNT ('STP_ALL_MBRS'[VENDOR]),
        FILTER (
            ALL ('STP_ALL_MBRS'[VENDOR]),
            'STP_ALL_MBRS'[VENDOR] > (STP_HEDIS_CUR[% Compliant] >= AVERAGE(STP_HEDIS_CUR[STAR4_CUTPOINT_OPTION1]))
                )
          )

[PhilipTreacy · Accepted solution]

@BrianPansy 

The issue is this line

'STP_ALL_MBRS'[VENDOR] > (STP_HEDIS_CUR[% Compliant] >= AVERAGE(STP_HEDIS_CUR[STAR4_CUTPOINT_OPTION1]))

What this is effectively doing is this

"a" > true/false

which will give you the error you have as you are comparing different data types.

You can't compare a text value to a boolean or numerical value so you need to rewrite that line to make sense.

Regards

Phil

[BrianPansy · Accepted solution]

It turns out I had a typo (that first > was supposed to be a ",") which threw the error, but even so my formula was counting members not measures. This is what I needed:

CALCULATE (
        DISTINCTCOUNT(STP_HEDIS_CUR[MEASURE_DESC]),
        FILTER (
            ALL (STP_HEDIS_CUR[MEASURE_DESC]), 
            STP_HEDIS_CUR[% Compliant] >= AVERAGE(STP_HEDIS_CUR[STAR4_CUTPOINT_OPTION1])
                )
        )

It's still incorrectly counting everything, but I'm pretty sure it's a de-duping issue from the source. Thank you for making me think this one through 😀.

[PhilipTreacy · Accepted solution]

@BrianPansy 

The issue is this line

'STP_ALL_MBRS'[VENDOR] > (STP_HEDIS_CUR[% Compliant] >= AVERAGE(STP_HEDIS_CUR[STAR4_CUTPOINT_OPTION1]))

What this is effectively doing is this

"a" > true/false

which will give you the error you have as you are comparing different data types.

You can't compare a text value to a boolean or numerical value so you need to rewrite that line to make sense.

Regards

Phil