powerbi - 如何基于 EnabledDate、EnabledDate + 1、EnabledDate + 2、EnabledDate + 7 和 power bi 上的日期过滤选项创建汇总表？

Question

我正在尝试根据日期范围内的 4 个条件创建汇总表

输入表

以文本形式设置的日期

Date        Name       Id Count Screen  Enabled Date
03-01-2020  Karthikeyan 1   4   Login   03-01-2020
03-01-2020  Karthikeyan 1   3   Logout  03-01-2020
04-01-2020  Karthikeyan 1   5   Chat    03-01-2020
05-01-2020  Karthikeyan 1   6   Logout  03-01-2020
05-01-2020  Sumit       2   9   Login   05-01-2020
05-01-2020  Sumit       2   5   Logout  05-01-2020
06-01-2020  Karthikeyan 1   4   Login   03-01-2020
07-01-2020  Karthikeyan 1   5   Chat    03-01-2020
08-01-2020  Karthikeyan 1   6   Logout  03-01-2020
06-01-2020  Sumit       2   7   Login   05-01-2020
07-01-2020  Sumit       2   8   Chat    05-01-2020
08-01-2020  Sumit       2   9   Logout  05-01-2020
09-01-2020  Karthikeyan 1   4   Login   03-01-2020
10-01-2020  Karthikeyan 1   5   Chat    03-01-2020
11-01-2020  Karthikeyan 1   6   Logout  03-01-2020
09-01-2020  Sumit       2   4   Login   05-01-2020
10-01-2020  Sumit       2   5   Chat    05-01-2020
11-01-2020  Sumit       2   6   Logout  05-01-2020

预期输出表

1) 选择 Distinct id, name (Group by id, name)

2）选择相对于日期范围的总计数

所有四个表格计算的日期范围

        (Enabled data) 
         03-01-2020     
         05-01-2020  

        (Enabled data) to  EnabledDate + 1
        StartDate          EndDate
        03-01-2020         04-01-2020      
        05-01-2020         06-01-2020       


       (Enabled data) to   EnabledDate + 2  
        StartDate          EndDate
        03-01-2020          05-01-2020      
        05-01-2020          07-01-2020      

       (Enabled data) to   EnabledDate + 7
        StartDate          EndDate
        03-01-2020         10-01-2020
        05-01-2020         11-01-2020(actual date is 12-01-2020 
but I don't have a value of end date so I consider the last date)

如果 Max date 不适用于日期范围，则认为最后一个日期是 Date 列中的结束日期。

3）根据计数选择Max Screen views 屏幕名称，如果两个屏幕具有相同的值，则选择第一个屏幕

4) 选择 Max Screen views count out of Total count

我以以下方式计算了启用计数，它产生了错误的结果

EnabledDateCount = 
    ADDCOLUMNS(
        ADDCOLUMNS(
            FILTER(
                SUMMARIZE(
                    EnabledDate,
                    EnabledDate[Id],EnabledDate[Name],EnabledDate[Count]
                ),
                EnabledDate[Name] <> ""
            ),
            "TotalCount", CALCULATE( SUM( EnabledDate[Count] ),
            FILTER(EnabledDate,EnabledDate[Date] >= MIN(EnabledDate[Enabled Date]) 
            && EnabledDate[Date] <= MAX(EnabledDate[Enabled Date]) )
        ),
        "Screen",
        VAR CurrentCount = [Count]
        RETURN CALCULATE( MIN(EnabledDate[Screen]), EnabledDate[Count] = CurrentCount )
    ),"ScreenCount",  CALCULATE( MAX(EnabledDate[Count]),
    FILTER(EnabledDate,EnabledDate[Date] >= MIN(EnabledDate[Enabled Date]) 
    && EnabledDate[Date] <= MAX(EnabledDate[Enabled Date]) ) ))

输出是

但是我们需要一个不同的用户 ID 和名称，而预期的结果不正确

标签： powerbipowerbi-desktoppowerbi-embeddedpowerbi-datasourcepowerbi-custom-visuals

Answer 1

最后，我按照以下方式创建了一个解决方案

启用日期摘要

enabledDateSummary = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ),enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] = enabledDate[Enabled Date]),[Count])), 
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary 输出：

启用日期摘要 + 1

enabledDateSummary + 1 = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ), enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] >= enabledDate[Enabled Date] && 
        enabledDate[Date] <= enabledDate[Enabled Date] + 1),[Count])),
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary + 1 输出：

启用日期摘要 + 2

enabledDateSummary + 2 = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ), enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] >= enabledDate[Enabled Date] && 
        enabledDate[Date] <= enabledDate[Enabled Date] + 2),[Count])),
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary + 2 输出：

启用日期摘要 + 7

enabledDateSummary + 7 = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ), enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] >= enabledDate[Enabled Date] && 
        enabledDate[Date] <= enabledDate[Enabled Date] + 7),[Count])),
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary + 7 输出：