Cumulative distinct count filtered by last value - DAX

Question

I have a dataset:

month   name    flag
1       abc     TRUE
2       xyz     TRUE
3       abc     TRUE
4       xyz     TRUE
5       abc     FALSE
6       abc     FALSE

I want to calculate month-cumulative distinct count of 'name' filtered by last 'flag' value (TRUE). I.e. I want to have a result:

month   count
1       1
2       2
3       2
4       2
5       1
6       1

In months 5 and 6 'abc' should be excluded because the flag switched to 'FALSE' in month 5. I am trying to achieve something using examples given here:

http://www.daxpatterns.com/cumulative-total/

.

But I am struggling terribly. I was thinking that maybe function TOPN could be used to filter the table on which DISTINCTCOUNT could be used but I am not getting the desired results.

Shouldn't your count in the given example be 1 for the months with no values for months 5 & 6? — cronos2546
? It should be 1 because there is 'xyz' with 'TRUE' in month 4. — zaptask

greggyb greggyb · Accepted Answer · 2016-01-26T20:59:34

MyFilteredCumulativeMeasure =
COUNTROWS(
    FILTER(
        GENERATE(
            ALL( 'MyTable'[name] )
            ,CALCULATETABLE(
                SAMPLE(
                    1
                    ,SUMMARIZE(
                        'MyTable'
                        ,'MyTable'[month]
                        ,'MyTable'[flag]
                    )
                    ,'MyTable'[month]
                    ,DESC
                )
                ,FILTER(
                    ALL( 'MyTable'[month] )
                    ,'MyTable'[month] <= MAX( 'MyTable'[month] )
                )
            )
        )
        ,'MyTable'[flag]
    )
)

This works on your sample. Might need some tweaking for the real data. Also likely to slow down significantly as data size increases. I'll continue noodling on it, because this doesn't feel quite right, but it's a good naive implementation.

Cumulative distinct count filtered by last value - DAX

1 Answers