Cumulative number of Rows in DAX

0
votes

I am trying to count the cumulative number of rows in Power Pivot

I am using the formula below:

FilteredRows =
VAR LatestYear =
MAX ( Rates[Year] )
VAR UnfilteredTable =
ALL ( Rates )
RETURN
COUNTROWS ( FILTER ( UnfilteredTable, Rates[Year] <= LatestYear ) )

enter image description here

Instead of getting the sequence you see in FilteredRows I am get 11 (The total number of rows in all the rows)

Any ideas?

1
excelpowerbidaxpowerpivot

1 Answers

0
votes

Try this:

FilteredRows =
COUNTROWS (
    SUMMARIZE (
        FILTER ( ALL ( 'Rates' ), 'Rates'[Year] <= MAX ( 'Rates'[Year] ) ),
        'Rates'[Year]
    )
)

When the years in 'Rates'[Year] don't appear more than once, you don't need the SUMMARIZE function. In that case this will also work:

FilteredRows = COUNTROWS ( FILTER ( ALL ( 'Rates' ), Rates[Year] <= MAX ( Rates[Year] ) ) )