请求将统计连续7次数的TSQL脚本转换为PowerBI的DAX脚本

需求说明 #

现有包含Number、Week、Counting字段的数据集，需完成以下操作：

• 统计每个Number对应的连续出现的7的次数
• 根据次数区间标记颜色：
  • 10-19次 → green
  • 20-29次 → blue
  • 30-52次 → red

已编写可正常运行的TSQL脚本，需转换为PowerBI适用的DAX脚本。

示例数据 #

原TSQL脚本 #

with test as (
    select Number,Week,Counting,row_number() over (order by (select null)) as rn
    FROM [vf_test_03] as t
),
LG as (
SELECT rn,Number, Counting, CASE WHEN Counting <> LAG(Counting) OVER(order by rn) THEN 1 ELSe 0 END lg
FROM test
), 
SU as (
SELECT rn,Number,Counting, SUM(lg) OVER(order by rn) su
FROM LG
),
test02 as (
SELECT Number,Counting, COUNT(*) as countt
FROM SU
GROUP BY Number, Counting,su
),
umisteni as (
select te.Number,te.Counting,te.countt,
    case when countt between 10 and 19 then 'green' 
         when countt between 20 and 29 then 'blue'
         when countt between 30 and 50 then 'red'
    end as Liga
from test02 as te
where Counting = 7
)
select Liga, Number
from umisteni
where Liga is not null
group by Liga,Number
order by Number

DAX实现方案 #

思路说明 #

和TSQL逻辑对齐，通过以下步骤实现：

1. 按Number分组、Week排序，识别连续7的分组边界
2. 累计分组标识，将连续相同的Counting归为同一组
3. 统计每组的连续次数，筛选出Counting=7的记录
4. 根据次数区间标记颜色，输出最终结果

完整DAX计算表代码 #

连续7次数统计 = 
VAR 带分组标识的表 = 
    ADDCOLUMNS (
        '你的表名', -- 替换为实际表名
        "分组标识",
        VAR 当前Number = '你的表名'[Number]
        VAR 当前Week = '你的表名'[Week]
        VAR 前一行Counting = 
            CALCULATE (
                MAX ( '你的表名'[Counting] ),
                FILTER (
                    '你的表名',
                    '你的表名'[Number] = 当前Number && '你的表名'[Week] = 当前Week - 1
                )
            )
        RETURN
            IF (
                '你的表名'[Counting] <> 前一行Counting || ISBLANK (前一行Counting),
                1,
                0
            )
    )
VAR 累计分组标识 = 
    ADDCOLUMNS (
        带分组标识的表,
        "累计分组",
        VAR 当前Number = [Number]
        VAR 当前Week = [Week]
        RETURN
            CALCULATE (
                SUM ([分组标识]),
                FILTER (
                    带分组标识的表,
                    [Number] = 当前Number && [Week] <= 当前Week
                )
            )
    )
VAR 连续次数统计 = 
    SUMMARIZE (
        累计分组标识,
        [Number],
        [Counting],
        [累计分组],
        "连续次数", COUNTROWS ( CURRENTGROUP () )
    )
VAR 标记颜色 = 
    ADDCOLUMNS (
        FILTER (连续次数统计, [Counting] = 7),
        "颜色标记",
        SWITCH (
            TRUE (),
            [连续次数] BETWEEN 10 AND 19, "green",
            [连续次数] BETWEEN 20 AND 29, "blue",
            [连续次数] BETWEEN 30 AND 52, "red",
            BLANK ()
        )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (标记颜色, NOT ISBLANK ([颜色标记])),
        "Number", [Number],
        "颜色标记", [颜色标记]
    )

使用说明 #

1. 将代码中的'你的表名'替换为PowerBI数据模型中的实际表名称
2. 在PowerBI「建模」选项卡点击「新建表」，粘贴上述代码即可生成统计结果表
3. 最终结果将展示符合条件的Number及其对应的颜色标记，与原TSQL脚本输出逻辑一致

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