SQL Analysis service query

[Angela000]

I would like to calculate patients retention rate, for any pathent who has visisted a practice in the first 6 months(for example:Jan, Feb, Mar, Apr, May, Jun) and returned in the next 6 months(for example:Jul, Aug, Sep, Oct, Nov, Dec), I created a new measure 'Pcount' as the below

Pcount:=
COUNTROWS(
    INTERSECT(
        CALCULATETABLE(
            DISTINCT(Appointment[pat_id]),
            Appointment[appointment_attended_count]=1,
             DATESINPERIOD('Date'[FullDate], EOMONTH(MAX('Date'[FullDate]),-1),-6,MONTH)
        ),
        CALCULATETABLE(
            DISTINCT(Appointment[pat_id]),
            Appointment[appointment_attended_count]=1,
            DATESINPERIOD('Date'[FullDate], EOMONTH(MAX('Date'[FullDate]),-7),-12,MONTH)
                                )
                            )
                       )

I depolyed it to the SQL Analysis Server successfully, and run the MDX query from SSMS, please see the image link (https://i.sstatic.net/XIqSSLJc.png), Got the error from SSMS: MdxScript(NDC_Model) (8, 2) This is not a valid DAX expression.

The cube is on SQL Analysis server version 2012 enter image description here

Just wondering is there any issue with the query or the server version is too low to support the SET operations (INTERSECT, Exception)? I know we can't declare variables(VAR) in version 2012.

[Anonymous · Accepted solution]

Hi @Angela000 ,

SQL Analysis server 2012 is already a very old version, and INTERSECT, EXCEPT and other functions belong to the advanced functions in DAX, may be the version really can not support all or part of the functions in the function.
Also, the error reported here already provides error localization, could you please check what function is used at that location? Or what syntax error exists in that location?

If you can, please change to a more advanced version of SSAS for testing, and have checked if the error is caused by the outdated version.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.