Operator or expression error in calculated table

[MichaelBauld]

Hi everyone, 

I have a formula which I use to calculate the total number of active memberships at any given date using the start date and end date of the membership to work this out. This works fine throughout my reporting as it stands, but I am trying to get a very specific set of DAX functions to work (the principle one being a set of expressions requiring this count data which are driving a visual showing the last 12 months of data from the selected date) and I am pretty sure having this data defined in a table like all the other datasets I have (where the intended visual works fine) will solve the problem. Sadly the same expression doesn't seem to work in a calculated table:

TOTAL Household Count = CALCULATE (

    COUNTX (

        FILTER (

            'Member_Warehouse',

            'Member_Warehouse'[validfrom] <= MAX ( 'Calendar MASTER'[Date] )

                && (

                    ISBLANK ( 'Member_Warehouse'[End Date using statecode])

                        || 'Member_Warehouse'[End Date using statecode] > MAX ( 'Calendar MASTER'[Date] )

                )

        ),

        ( 'Member_Warehouse'[Index] )

    ),

    CROSSFILTER ('Member_Warehouse'[validfrom],'Calendar MASTER'[Date], NONE)

)

When I try to use the same DAX formula within a calculated table I get the following error:

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

I am simply trying to build a calculated table which delivers the same results as the existing DAX (i.e total number of active memberships for each date in the table):

I haven't really tried to build any calculated tables before so am not hugely familar with rules around this, so any help woudl be great appreciated!

Many thanks

Michael

[bhanu_gautam · Accepted solution]

@MichaelBauld , Use the below DAX for new column

DAX
ActiveMembershipsTable =
ADDCOLUMNS (
'Calendar MASTER',
"Total Household Count",
VAR CurrentDate = 'Calendar MASTER'[Date]
RETURN
SUMX (
FILTER (
'Member_Warehouse',
'Member_Warehouse'[validfrom] <= CurrentDate
&& (
ISBLANK ( 'Member_Warehouse'[End Date using statecode])
|| 'Member_Warehouse'[End Date using statecode] > CurrentDate
)
),
1
)
)