Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Trystan
Frequent Visitor

Query optimization help needed

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. 

Trystan_0-1683764330382.png

Trystan_1-1683764466328.png

 

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

Trystan_2-1683764934456.png

 

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

 

Thanks.

 

1 REPLY 1
johnt75
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.