精品丨DAX性能优化问题

BOSS：那个，白茶啊，这个报表刷新有点慢啊，你看，每次我点筛选或者刷新都会让我等很久。
白茶：（认真脸）BOSS，那您再等会就好！
......
BOSS：白茶！！两小时了！！还没出来！！
白茶：（思考）老板，这个有点难啊，这个问题技术要求比较高。
BOSS：加钱！！！！
白茶：好嘞！

一张好的报表是如何界定的？DAX计算无误、前端展现明了、业务思路清晰、报表响应速度，白茶觉得这些因素就可以界定一张好的报表。

本期我们来聊一聊PowerBI中DAX函数性能优化的问题。

毕竟一张可视化报表需要15分钟刷新才能呈现出来，这对用户来说太不友好了。

先来看看本期的示例文件：

一张产品维度表，一张销售明细表。

需求是什么？

这张是销售明细表中的分店维度信息，为了便于小伙伴理解，白茶单独整理出来。

这张表是需求的计算逻辑图。什么意思呢？就是当Key小于15时，计算每个Key对应的分店，当Key大于14时，根据计算逻辑对不同的分店进行汇总计算。

编写基础的DAX计算代码：

SalesAmount =
SUMX ( 'Fact_SalesDetail', [Quantity] * RELATED ( Dim_Product[SalesAmount] ) )

在不考虑性能的情况下，DAX计算逻辑如下：

SalesAmountByDisplay = 
SUMX (
    'Dim_DisplayDepartment',
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 1,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 1 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 2,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 2 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 3,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 3 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 4,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 4 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 5,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 5 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 6,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 6 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 7,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 7 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 8,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 8 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 9,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 9 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 10,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 10 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 11,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 11 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 12,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 12 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 13,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 13 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 14,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 14 )
            ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 15,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 13 )
            )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 14 )
                ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 16,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 1 )
            )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 7 )
                )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 9 )
                ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 17,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 1 )
            )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 2 )
                )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 3 )
                )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 11 )
                ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 18,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 9 )
            )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 11 )
                )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 12 )
                )
                - CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 2 )
                ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 19,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 4 )
            )
                + CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 7 )
                ),
        SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] ) = 20, CALCULATE ( [SalesAmount], ALLSELECTED ( Fact_SalesDetail ) )
    )
)

相信不用白茶多说，小伙伴也能看出来代码的问题，太长了。

这段代码功能基本上是实现了，问题点有哪些呢？

1.SELECTEDVALUE复用度较高，可以使用变量代替

2.多个条件汇总迭代次数较多，可以使用提供List

这也是小伙伴常见的问题，如果DAX的构建可以绕开这两个问题，那么性能会有很大的提升。

简化版写法：

SalesAmountByDisplay2 = 
VAR CurrentDepartmentKey =
    SELECTEDVALUE ( Dim_DisplayDepartment[DepartmentKey] )
VAR CurrentDepartment =
    TREATAS (
        VALUES ( Dim_DisplayDepartment[DepartmentKey] ),
        Fact_SalesDetail[DepartmentKey]
    )
VAR Results =
    SWITCH (
        TRUE (),
        CurrentDepartmentKey = 15,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] IN { 13, 14 } )
            ),
        CurrentDepartmentKey = 16,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] IN { 1, 7, 9 } )
            ),
        CurrentDepartmentKey = 17,
            CALCULATE (
                [SalesAmount],
                FILTER (
                    'Fact_SalesDetail',
                    'Fact_SalesDetail'[DepartmentKey] IN { 1, 2, 3, 11 }
                )
            ),
        CurrentDepartmentKey = 18,
            CALCULATE (
                [SalesAmount],
                FILTER (
                    'Fact_SalesDetail',
                    'Fact_SalesDetail'[DepartmentKey] IN { 9, 11, 12 }
                )
            )
                - CALCULATE (
                    [SalesAmount],
                    FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 2 )
                ),
        CurrentDepartmentKey = 19,
            CALCULATE (
                [SalesAmount],
                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] IN { 4, 7 } )
            ),
        CurrentDepartmentKey = 20, CALCULATE ( [SalesAmount], ALLSELECTED ( Fact_SalesDetail ) ),
        CALCULATE ( [SalesAmount], CurrentDepartment )
    )
RETURN
    Results

相较于之前的写法，这个写法通过VAR定义变量，和使用TREATAS来减少代码计算逻辑的书写。

通过提供List来减少迭代遍历的次数。

那么有没有继续可以优化的空间？有的。

优化写法：

SalesAmountByDisplay3 = 
VAR CurrentDetail =
    ADDCOLUMNS (
        DISTINCT ( 'Dim_DisplayDepartment' ),
        "@CurrentValue",
            VAR CurrentDepartmentKey = 'Dim_DisplayDepartment'[DepartmentKey]
            RETURN
                SWITCH (
                    TRUE (),
                    CurrentDepartmentKey = 15,
                        CALCULATE (
                            [SalesAmount],
                            FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] IN { 13, 14 } )
                        ),
                    CurrentDepartmentKey = 16,
                        CALCULATE (
                            [SalesAmount],
                            FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] IN { 1, 7, 9 } )
                        ),
                    CurrentDepartmentKey = 17,
                        CALCULATE (
                            [SalesAmount],
                            FILTER (
                                'Fact_SalesDetail',
                                'Fact_SalesDetail'[DepartmentKey] IN { 1, 2, 3, 11 }
                            )
                        ),
                    CurrentDepartmentKey = 18,
                        CALCULATE (
                            [SalesAmount],
                            FILTER (
                                'Fact_SalesDetail',
                                'Fact_SalesDetail'[DepartmentKey] IN { 9, 11, 12 }
                            )
                        )
                            - CALCULATE (
                                [SalesAmount],
                                FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] = 2 )
                            ),
                    CurrentDepartmentKey = 19,
                        CALCULATE (
                            [SalesAmount],
                            FILTER ( 'Fact_SalesDetail', 'Fact_SalesDetail'[DepartmentKey] IN { 4, 7 } )
                        ),
                    CurrentDepartmentKey = 20, CALCULATE ( [SalesAmount], ALLSELECTED ( Fact_SalesDetail ) ),
                    CALCULATE (
                        [SalesAmount],
                        'Fact_SalesDetail'[DepartmentKey] = CurrentDepartmentKey
                    )
                )
    )
RETURN
    SUMX ( CurrentDetail, [@CurrentValue] )

这种写法，相较于上一种书写量多一些，通过定义虚拟表来减少迭代遍历的次数。从理论上来说，因为定义了虚拟表，无论源代码需要对事实表迭代多少次，这个思路迭代的永远都是虚拟表，优化度很高。

我们来对比一下：

DAX Studio测试：

三种写法的差距很明显。其实这里有一些争议的，简化写法是通过减少资源占用来实现优化，优化写法是通过减少迭代遍历实现优化。

从测试结果上来看，是简化写法优化度较高，但是在实际应用中，测试发现优化写法的方式响应更迅速。

你以为到这里就结束了么？

其实还有第四种优化的思路，只不过这个思路比较难。

SalesAmountByDisplay4 = 
SUMX (
    VALUES ( 'Dim_DisplayDepartment'[DepartmentKey] ),
    VAR CurDpmKey = 'Dim_DisplayDepartment'[DepartmentKey]
    VAR TempTable =
        FILTER (
            ALL ( 'Dim_DisplayDepartment'[DepartmentKey] ),
            'Dim_DisplayDepartment'[DepartmentKey] <= 14
        )
    VAR AllDetail =
        ADDCOLUMNS (
            TempTable,
            "SalesAmount",
                VAR CurrentDepartment = 'Dim_DisplayDepartment'[DepartmentKey]
                RETURN
                    CALCULATE (
                        [SalesAmount],
                        ALL ( 'Dim_DisplayDepartment' ),
                        'Fact_SalesDetail'[DepartmentKey] = CurrentDepartment
                    )
        )
    VAR FilterContent =
        CALCULATE (
            MAX ( 'Dim_ComputationalLogic'[FilterContent] ),
            ALL ( Dim_DisplayDepartment ),
            'Dim_ComputationalLogic'[DepartmentKey] = CurDpmKey
        )
    VAR Length =
        LEN ( FilterContent )
    VAR FilterTable =
        SELECTCOLUMNS (
            GENERATESERIES ( 1, Length, 4 ),
            "DepmKey", MID ( FilterContent, [Value], 3 ) * 1
        )
    VAR Result =
        SUMX (
            FilterTable,
            VAR DpmKey = [DepmKey]
            VAR SalesValue =
                SUMMARIZE (
                    FILTER ( AllDetail, 'Dim_DisplayDepartment'[DepartmentKey] = ABS ( DpmKey ) ),
                    [SalesAmount]
                )
            RETURN
                IF ( DpmKey >= 0, SalesValue, - SalesValue )
        )
    RETURN
        Result
)
--作者：夕枫

这个优化的思路，是@夕枫大佬提出来的。通过定义计算表，减少代码书写量，使用ALL减少上下文转换的消耗，减少查询次数，命中缓存。

DAX Studio测试：

总结一下：

1.可以通过变量和定义表来减少代码书写量

2.可以通过减少资源调用优化

3.可以通过虚拟表减少迭代遍历

4.可以通过命中缓存进行优化

比较常用的是前三种，第四种难度系数较高。

往期推荐：

小伙伴们❤GET了么？

（白茶：别问我第四种，我不会TAT）

这里是白茶，一个PowerBI的初学者。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,734评论 6赞 505
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,931评论 3赞 394
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,133评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,532评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,585评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,462评论 1赞 302
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,262评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,153评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,587评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,792评论 3赞 336
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,919评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,635评论 5赞 345
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,237评论 3赞 329
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,855评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,983评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,048评论 3赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,864评论 2赞 354

精品丨DAX性能优化问题

推荐阅读更多精彩内容