时间戳

首页 > 解决方案 > 如何使用时间变量增强组合代码

powerbi - 如何使用时间变量增强组合代码

问题描述

我通过 v-shex-msft 找到了以下代码，我想用它来提供一个组合列表。向他致敬。现在我想添加一个时间变量，以便仅组合 24 小时内发生的事件。不幸的是，过滤器功能不允许我使用更多参数：

原始代码：

Summary Table =

var temp= 
SUMMARIZE(
    Sheet5,
    Sheet5[Customer],
    "Combinations",CONCATENATEX(
        FILTER(
            SUMMARIZE(
                Sheet5,
                [Customer],
                Sheet5[Type]
            ),
            Sheet5[Customer] = EARLIER(Sheet5[Customer])
        ),
        [Type]&","
    )
)

return
SUMMARIZE(
    temp,
    [combinations],
    "Number",COUNTAX(
        FILTER(
            temp,
            [combinations]=EARLIER([combinations])
        ),
        [Customer]
    )
)

我尝试按如下方式添加时间变量：

 Summary Table =

var temp = 
SUMMARIZE(
    Sheet5,
    Sheet5[Customer],
    "Combinations",CONCATENATEX(
        FILTER(
            SUMMARIZE(
                Sheet5,
                [Customer],
                Sheet5[Type], 
                Sheets5[time]
            ),
            Sheet5[Customer] = EARLIER(Sheet5[Customer]) 
            && Sheets5[time]+1 >= Earlier(Sheets5[time]) 
            && Sheets5[time]-1 <= Earlier(Sheets5[time])
        ),
        [Type]&","
    )
)

return
SUMMARIZE(
    temp,
    [combinations],
    "Number",COUNTAX(
        FILTER(
            temp,
            [combinations] = EARLIER([combinations])
        ),
        [Customer]
    )
)

关于出了什么问题的任何想法？非常感谢您的帮助。

标签： powerbicombinationsmarket-basket-analysis

解决方案

你可以试试下面的代码 -

 Summary Table =

var temp = 
SUMMARIZE(
    Sheet5,
    Sheet5[Customer],
    "Combinations",CONCATENATEX(
        FILTER(
            SUMMARIZE(
                Sheet5,
                [Customer],
                Sheet5[Type], 
                Sheets5[time]
            ),
            MAX(Sheet5[Customer]) = EARLIER(Sheet5[Customer]) 
            && MAX(Sheets5[time]) +1 >= Earlier(Sheets5[time]) 
            && MAX(Sheets5[time]) -1 <= Earlier(Sheets5[time])
        ),
        [Type]&","
    )
)

return
SUMMARIZE(
    temp,
    [combinations],
    "Number",COUNTAX(
        FILTER(
            temp,
            [combinations] = EARLIER([combinations])
        ),
        [Customer]
    )
)

推荐阅读