Error: DAX comparison operations do not support comparing values of type true/false

[poojik]

I am trying to create a new calcuated field to count all the account names if "ValidationStatus" is TRUE. The formula I am using is : 

Validated count = CALCULATE(DISTINCTCOUNT(accounts[name]),FILTER(accounts,VALUE(accounts[validationstatus])= TRUE()))

I am getting this error : 

DAX Comparision operations do not support comparing values of type Number with values of type True/False. Consider using value or FORMAT function to convert one of the values. 

Any help will be appriciated. 

Best

P

[Anonymous]

Can someone help me please. I have the following code and I keep getting this error. I have tried wrapping the calculate statement in a VALUE function and I get the message, cannot convert true/false.

Inpatient comment =

var newline2 =

"

"

return

if(

CALCULATE([Per Member % Change],'Benefit Mapping'[Benefit Type]="inpatient">0.05),"Inpatient Costs:" & newline2 & "The average inpatient cost per member has increased year-over-year by " &FORMAT(CALCULATE([Per Member % Change],'Benefit Mapping'[Benefit Type]="inpatient"), "currency")& ". IP costs are sporadic in nature and can significantly increase or decrease overall costs; in this instance, IP increased overall costs",

if (

AND(CALCULATE([Per Member % Change],'Benefit Mapping'[Benefit Type]="inpatient")<=0.05,CALCULATE([Per Member % Change],'Benefit Mapping'[Benefit Type]="inpatient")>=-0.05 ),"inpatient Costs: " & newline2 & "The average inpatient cost per member has been roughly stable year-over-year (" &FORMAT(CALCULATE([Per Member % Change],'Benefit Mapping'[Benefit Type]="inpatient"), "currency")& " change year-over-year. IP costs are sporadic in nature and can significantly increase or decrease overall costs; in this instance, IP had minimal impact on overall costs",

"Inpatient Costs:" & newline2 & "The average inpatient cost per member has decreased year-over-year by " &FORMAT(CALCULATE([Per Member % Change],'Benefit Mapping'[Benefit Type]="inpatient"), "currency")& ". IP costs are sporadic in nature and can significantly increase or decrease overall costs; in this instance, IP reduced overall costs"

))

[Vvelarde]

@poojik

Hi try with this

Validated Count =
CALCULATE (
    DISTINCTCOUNT ( Accounts[name] );
    FILTER ( Accounts; Accounts[validationstatus] = TRUE () )
)

Regards

Victor

[poojik]

@Vvelarde

This was the first thing I tried previously but it didn't work. 

[Vvelarde]

@poojik

Double Check the Data Type

With this this work:

Regards

Victor

[poojik]

The data type in Power BI desktop is True/False and the solution 

Validated Count =
CALCULATE (
    DISTINCTCOUNT ( Accounts[name] );
    FILTER ( Accounts; Accounts[validationstatus] = TRUE () )
)

works for power BI desktop. But when I publish the report on Power BI pro online. The data values chages to 0 and -1 hence the solution doesn't work.

I am not able to edit the expression in power BI pro to make it compatible. 

[v-jiascu-msft]

Hi @poojik,

Can you share a sample of the data?

Best Regards,

Dale

[poojik]

Hi @v-jiascu-msft 

The test data is attached. The data is downloaded from Dynamics CRM accounts entity. There are 6 fields with data in 14 rows :

1. Name : Text field

2. Validation status : Option set

3. Created on : Date and Time field

4. Rsubmitted : Option set

5. Units : Whole Number

6. Region : Text field 

'

Now because I have to find out the absolute percentage of names that has Rsubmitted as "Yes"  (example out of 4 Toronto records 3 has Rsubmitted as "Yes". Hence the percentage is 75%)

To find out hte percentage i have created a calcuated field : 

Validated count =CALCULATE(DISTINCTCOUNT(accounts[name]),accounts[Rsubmitted]= "TRUE")

Absolute_percentage = (([Validated count])/(count(accounts[Name])))

The calculations are valid and am able to plot stacked bar graph (Screen-shot attached)

But when I try to publish this graph to Power BI pro- the data type for Rsubmitted" changes from TURE/FLASE to 1/0 (Screen-shot attached)

[Anonymous]

Try this:

CALCULATE(DISTINCTCOUNT(accounts[name]),accounts[validationstatus])= "TRUE")

[poojik]

I am still getting the same error. Screen-shot attached.