Cumulative distinct count filtered by last value -

问题:

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.

回答1:

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.