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

[cherimjewell]

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")))

        ,"")

[v-yuta-msft]

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]

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]

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

[cherimjewell]

I changed those two columns to be numbers (dollars and decimal number to be exact) and it's still not doing what I want it to do.

I've recently taken a formula from Excel that works exactly and modifying it to include the field names instead of cell references. When I put it into a measure it gives me the wrong status. 

i.e. Prior Month = $500.00, Selected Month = $500.00 should be "Retained", but i have instances where there is no change in amounts that are reporting as "Retained-Upgrade" and "Retained-Downgrade". This seems to be the only issue that I'm having.

The formula is:

Customer Sales Status C = if(and(REVENUE[Selected Month Revenue]=REVENUE[Prior Month Revenue],REVENUE[Selected Month Revenue]<>0),"Retained",if(and(REVENUE[Selected Month Revenue]>REVENUE[Prior Month Revenue],REVENUE[Prior Month Revenue]<>0),"Retained-Upgrade",if(and(and(REVENUE[Selected Month Revenue]<REVENUE[Prior Month Revenue],REVENUE[Prior Month Revenue]<>0),REVENUE[Selected Month Revenue]<>0 ),"Retained-Downgrade",if(and(REVENUE[Selected Month Revenue]>REVENUE[Prior Month Revenue],REVENUE[Prior Month Revenue]=0),"New",if(and(REVENUE[Selected Month Revenue]=0,REVENUE[Prior Month Revenue]=0),"Omit",if(and(REVENUE[Selected Month Revenue]=0,REVENUE[Prior Month Revenue]>0),"Cancelled"))))))