Forum Discussion

cherimjewell's avatar
cherimjewell
Frequent Visitor
8 years ago

Operator or expression '()' is not supported in this context.

Hello! I am pretty new to PBI Desktop and am trying to figure out how to create a measure that does many things. Its a list of calculations with an additional parameter and I need to return a text status. Below is my formula. I've been messing with it for a couple of days. I'm getting the error in the subject, but I don't know where the () is. Any help would be fantastic!

 

Customer Sales Status = SWITCH(

        (CALCULATE(Revenue[Sales in Period]=Revenue[Prior Month Sales])&&(Revenue[Sales in Period]<>0),"Retained")

        ,AND(IF((CALCULATE(Revenue[Sales in Period]>Revenue[Prior Month Sales])&&(Revenue[Prior Month Sales]<>0),"Retained-Upgrade")))

        ,AND(IF((CALCULATE(Revenue[Sales in Period]<Revenue[Prior Month Sales])&&(Revenue[Prior Month Sales]<>0,Revenue[Sales in Period]<>0),"Retained-Downgrade")))

        ,AND(IF((CALCULATE(Revenue[Sales in Period]>Revenue[Prior Month Sales])&&(Revenue[Prior Month Sales]=0),"New")))

        ,AND(IF((Revenue[Sales in Period]=0)&&(,Revenue[Prior Month Sales]=0),"Omit"))

        ,AND(IF(Revenue[Sales in Period]=0,AND(IF(Revenue[Prior Month Sales]>0,),"Cancelled")))

        ,"")

4 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi cherimjewell,

     

    Modify your calculate column like this and check if it can meet your requirement;

    Customer Sales Status =
SWITCH (
    Revenue[Sales in Period] = Revenue[Prior Month Sales]
        && Revenue[Sales in Period] <> 0,
    "Retained", Revenue[Sales in Period] > Revenue[Prior Month Sales]
        && Revenue[Prior Month Sales] <> 0,
    "Retained-Upgrade", Revenue[Sales in Period] < Revenue[Prior Month Sales]
        && Revenue[Prior Month Sales] <> 0
        && Revenue[Sales in Period] <> 0,
    "Retained-Downgrade", Revenue[Sales in Period] > Revenue[Prior Month Sales]
        && Revenue[Prior Month Sales] = 0,
    "New", Revenue[Sales in Period] = 0
        && Revenue[Prior Month Sales] = 0,
    "Omit", Revenue[Sales in Period] = 0
        && Revenue[Prior Month Sales] > 0,
    "Cancelled", ""
)

    Regards,

    Jimmy Tao

    • cherimjewell's avatar
      cherimjewell
      Frequent Visitor
      8 years ago

      Thank you so much! I appreciate your response.

      I don't get an error on the measure any longer, but I'm getting an error when adding it to a table visual:

       

      MDXScript(Model) (45,5) Calculation error in measure 'Revenue'[Customer Sales Status]: Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

       

      I've tried several variations (IF(AND, SWITCH(TRUE(), VALUE, etc) to get this to work, yours is the only one that didn't produce an error. Any other suggestions? 

      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi cherimjewell,

         

        As the error message said, you should convert columns Revenue[Sales in Period] and Revenue[Prior Month Sales] into number type which can be compared.

         

        Regards,

        Jimmy Tao