通过上一个值过滤的累计非重复计数-DAX [英] Cumulative distinct count filtered by last value - DAX

问题描述

我有一个数据集:

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

我想计算按标志"值(真)过滤的名称"的月份累计唯一计数. IE.我想要一个结果:

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

在第5个月和第6个月，不应将'abc'排除在外，因为标志在第5个月切换为'FALSE'.

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

.

但是我在努力挣扎.我当时在想，也许可以使用功能TOPN来过滤可以在其上使用DISTINCTCOUNT的表，但是我没有得到期望的结果.

解决方案

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]
    )
)

这适用于您的样本.可能需要对实际数据进行一些调整.随着数据大小的增加，也有可能显着降低速度.我将继续说明这一点，因为这感觉不太正确，但这是一个很好的幼稚实现.

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.

解决方案

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.

这篇关于通过上一个值过滤的累计非重复计数-DAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！