Forum Discussion

tdailey77's avatar
tdailey77
Frequent Visitor
6 years ago
Solved

DAX comparison - VALUE OR FORMAT

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

Data Protection
  • amitchandak's avatar
    amitchandak
    6 years ago

    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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      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's avatar
      tdailey77
      Frequent Visitor
      6 years ago

      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