Query optimization help needed

[Trystan]

Background

I currently have three measures, each used by the previous to achieve an outcome. I am certain this could be done in a more efficient way and am looking for help to get there. 

 

The Data

Here is some made-up data that includes sales, product changes and terminations. 

 

Measures

The goal is to see what the membership base "looks like" at any point in time dynamically, i.e. it should only see the most recent row for a given member key. I also need to be able to see the cumulative count at any point in time.

Here are the measures which are giving me the desired result, but the performance is very slow on real data.

1 - Current Row = 
    VAR _Current = SELECTEDVALUE('Members'[MemberKey])
    VAR _Date = MAXX(
        FILTER(
            ALLSELECTED('Members'), 
            'Members'[MemberKey] = _Current
            ), 
        RELATED('Date'[Date])
        )
     
RETURN    
  CALCULATE(
            COUNTROWS('Members'),
            'Members'[MemberKey] = _Current,
            'Date'[Date] = _Date
        )


2 - Member Count At Date = 
CALCULATE(
    COUNTROWS('Members'),
    FILTER('Members', [1 - Current Row] = 1),
    'Members'[MemberStatus] = "active",
    'Date'[Date] <= MAX('Date'[Date])
    )

3 - Cumulative Member Count At Date = 
CALCULATE(
    [2 - Member Count At Date],
    ALL('Date'),
    'Date'[Date] <= MAX('Date'[Date])
)

 

Expected output

I have tried to upload the minimal example I created but it seems I do not have permission to do that yet...

Thanks.

[johnt75]

You can try the below. You will need to have a column which has unique values for each row. If your model doesn't have one you can use Power Query to add an index column. When you have the column, you need to go into modelling view and mark that column as the key for the table.

Active members at date =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    CALCULATETABLE (
        INDEX (
            1,
            Members,
            ORDERBY ( Members[Date], DESC ),
            PARTITIONBY ( Members[Member Key] )
        ),
        'Date'[Date] <= ReferenceDate
    )
VAR Result =
    COUNTROWS ( FILTER ( SummaryTable, [Member Status] = "active" ) )
RETURN
    Result