DAX comparison - VALUE OR FORMAT

[tdailey77]

Hello,

I have my data source connected to Microsoft D365 Online, I used to use On-Prem that was connected to a SQL server. I am finding this change to be a huge pain in the %$@!

In my old report I was using the formula below to calculate the # of people that show up for their appointments:

Show = COUNTROWS(filter(FilteredOpportunity,FilteredOpportunity[Appt Result]="Loss - No Sale" || FilteredOpportunity[Appt Result]="No Loss" || [Appt Result]="Medical Referral" || [Appt Result]="Sold" || [Appt Result]="Sold - Competitor Product" || FilteredOpportunity[Appt Result]="Sold - Return" || [Appt Result]="Show Pending Results" || [Appt Result]="Insurance" ))+0

Now in the new data source report, I'm trying to use that same type of formula but with the new table names, etc:

Total Show =

COUNTROWS(filter(Opportunities,Opportunities[po_opportunitydisposition]="936710003" || Opportunities[po_opportunitydisposition]="936710002" || Opportunities[po_opportunitydisposition]="936710005" || Opportunities[po_opportunitydisposition]="936710004" || Opportunities[po_opportunitydisposition]="936710008" || Opportunities[po_opportunitydisposition]="936710010" || Opportunities[po_opportunitydisposition]="936710007" || Opportunities[po_opportunitydisposition]="936710009" ))+0

I'm getting the following error: 

Error Message:
MdxScript(Model) (54, 32) Calculation error in measure 'Opportunities'[Total Show]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

I have no idea what this means, can someone help? Thank you

[amitchandak · Accepted solution]

@tdailey77 , is po_opportunitydisposition is text column or whole number. Seem like the whole number. If so remove double quotes

example

Opportunities[po_opportunitydisposition]=936710003

[tdailey77]

Thank you, this solved it!! Very much appreciate the quick response too! I've been stuck on this for weeks, I guess I know where to go for help now. 🙂

Tim

[parry2k]

@tdailey77 as @amitchandak suggested, if it is a number, don't add quotes but also I would recommend to use IN statement for easy readability

Total Show =
COUNTROWS(filter(Opportunities,Opportunities[po_opportunitydisposition] IN {
"936710003" ,
"936710002",
"936710005",
"936710004" ,
"936710008",
"936710010",
"936710007",
"936710009" }
)
)+0

or

Total Show =
COUNTROWS(filter(Opportunities,Opportunities[po_opportunitydisposition] IN {
936710003,
936710002,
936710005,
936710004,
936710008,
936710010,
936710007,
936710009 }
)
)+0

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[tdailey77]

Thank you for the suggestion!