如何用DAX/PowerQuery/计算表判定指定日期的活跃与非活跃用户

问题背景 #

现有事实表和Dim User维度表，需要统计2025年6月28日所有用户的活跃状态（1=活跃，0=非活跃），输出包含所有用户ID、活跃状态及日期的列表，并可基于此统计活跃/非活跃用户数量。

数据说明 #

事实表

ID USER    ACTIVE USER ID    DATE        IF.ACTIVE
 -             -             6/28/2025      0 (非活跃用户无有效ID记录)
 -             -             6/28/2025      0
 -             -             6/28/2025      0
123456      123456           6/28/2025      1

Dim User维度表

ID USER
-------
123456
234567
345678
456789

解决方案 #

方法一：DAX计算表 #

通过创建DAX计算表，生成所有用户在目标日期的活跃状态：

用户活跃状态统计 = 
VAR 目标日期 = DATE(2025, 6, 28)
VAR 所有用户日期组合 = CROSSJOIN('Dim User', {目标日期})
VAR 当日活跃用户 = SELECTCOLUMNS(
    FILTER('事实表', '事实表'[DATE] = 目标日期 && '事实表'[IF.ACTIVE] = 1),
    "ID USER", '事实表'[ID USER],
    "活跃标记", 1
)
RETURN
ADDCOLUMNS(
    所有用户日期组合,
    "is.Active", 
    VAR 当前用户 = [ID USER]
    RETURN COALESCE(LOOKUPVALUE(当日活跃用户[活跃标记], 当日活跃用户[ID USER], 当前用户), 0),
    "Date", 目标日期
)

逻辑说明：

1. 定义目标日期2025-06-28；
2. 用CROSSJOIN生成维度表所有用户与目标日期的笛卡尔积；
3. 筛选事实表中当日活跃的用户记录；
4. 为每个用户匹配活跃状态，未匹配到活跃记录的用户标记为0。

方法二：Power Query处理 #

通过Power Query完成数据清洗与关联：

let
    // 加载维度表和事实表
    DimUser = Excel.CurrentWorkbook(){[Name="DimUser"]}[Content],
    FactTable = Excel.CurrentWorkbook(){[Name="FactTable"]}[Content],
    
    // 筛选2025-06-28的活跃用户，整理列
    活跃用户表 = Table.SelectRows(FactTable, each [DATE] = #date(2025,6,28) and [IF.ACTIVE] = 1),
    活跃用户整理 = Table.SelectColumns(Table.AddColumn(活跃用户表, "is.Active", each 1), {"ID USER", "is.Active"}),
    
    // 给维度表添加目标日期
    维度表加日期 = Table.AddColumn(DimUser, "Date", each #date(2025,6,28)),
    
    // 左连接维度表与活跃用户表
    合并表 = Table.NestedJoin(维度表加日期, {"ID USER"}, 活跃用户整理, {"ID USER"}, "活跃用户信息", JoinKind.LeftOuter),
    
    // 展开列并替换空值为0
    展开表 = Table.ExpandTableColumn(合并表, "活跃用户信息", {"is.Active"}, {"is.Active"}),
    最终表 = Table.ReplaceValue(展开表, null, 0, Replacer.ReplaceValue, {"is.Active"}),
    
    // 调整列顺序
    调整列序 = Table.ReorderColumns(最终表, {"ID USER", "is.Active", "Date"})
in
    调整列序

操作步骤：

1. 将两个表导入Power Query；
2. 筛选事实表中当日活跃的用户，保留ID并添加活跃标记；
3. 为维度表添加目标日期；
4. 左连接维度表与活跃用户表，将未匹配到的活跃状态设为0；
5. 调整列顺序后加载结果。

预期输出 #

基于输出表，可通过简单统计（如COUNTIF或DAX的COUNTROWS+FILTER）得到活跃/非活跃用户数量。

内容的提问来源于stack exchange，提问作者symkly