从汇率转换通用解决方案到可复用设计思想

汇率转换中，有很多问题，当然这个场景基本发生在有国际化运营情况的企业中。

大家好，我是 BI 佐罗，这里和大家一起探讨这个问题的通用解决方案。

场景抽象

先来抽象出该场景的通用结构。大致如下：

• 汇率每天都在变化，因此，会有一个随日期变化的汇率表。
• 交易表则整合来含有不同汇率的交易值。

从本质上来说，交易值的问题在于：

• 值
• 单位

问题就是值的单位是不统一的。

这在业务上带来的问题就是：

日本市场和欧洲市场的总交易是 1.2 亿，什么，单位是什么，如果不对单位做处理，那单位相当于是混合的，也就失去了意义。

因此，汇率转换的基本业务意义和核心业务意义在于：

将值的单位统一在一个单位。

另一方面，随着看报告的角色不同，可能是不同国家的有权限的人，可能会存在用不同单位视角来看结果的诉求，例如：

分别按 RMB 或 USD 来看结果。

综上，汇率转换的本质在于统一单位，实现：

• 业务逻辑上的值的单位统一
• 查看报告时的单位可变

相通了这些问题，再来看看其中的难点。

难点分析

由于结算发生在交易时，因此，随着汇率的时效性，在统一单位时，与 K，M，B 的不同在于，这种统合单位是随着时间而变化的。

某天的交易额要按当天（或业务上合规的结算汇率）来统一到统一单位尺度。

通用数据模型结构

根据以上分析，可知必须具备涵盖以上信息的相关表，如下：

• 汇率表
• 交易表

以及以上两表涉及的规范化形态。具体数据模型大致如下：

这个数据模型的搭建，非常有学问。这里先知道用到的表，后续再来进一步解释这个设计。

汇率表

汇率表的最小通用结构，如下：

这里强调了一种演示，具体汇率值可能并不真实（例如：JPY 日元对人民币 RMB 的汇率），但并不影响对解决方案的理解。

为了表示更复杂的场景以及更清晰的逻辑，该表所在案例的业务含义如下：

• 汇率表示从 A 到 B 的转换系数，例如：交易了 1 美元，则可以直接匹配 From 端后，再乘以 To 端即可得目标值。
• 汇率并不是每天都记录的，在业务上要求计算时需要匹配最后一个可用的汇率作为结算汇率。

这样的处理使得该案例有更一般的假设，可以满足任何场景。

交易表

交易表的最小通用结构，如下：

其中，CurrencyId 表示汇率的 Id，可与汇率定义进行匹配。

汇率定义表

汇率定义表的最小通用结构，如下：

这里将 RMB 作为统一的基准，并称为 Normal，而其他的货币都称为 Extend，以便于操作。

基础指标

表示交易的基础指标，定义为：

KPI.Value.Base = 

SUM( Fact_Sales[Value] )

其中，.Base 后缀暗示了该指标的基础性，且不能直接使用，并需要进一步改进，以便可以计算。

设计模式 - 封装与继承

有过 “面向对象设计” 程序设计基础的伙伴可以理解一个概念，在面向对象设计的语言中，有三个特性：

• 封装
• 继承
• 多态

DAX 与面向对象设计的机制是毫无关系的，但并不影响我们借用某些思想来设计和思考问题，以便让解决方案具有：

• 更好的通用性
• 更强的扩展性
• 更易的理解性

?提示 这里首次提出：利用 DAX 的基本特性，可以完全按照封装，继承，多态的思想来设计解决方案。这为学习，体验 DAX 的美以及用于实际工程项目具有重要意义。

DAX 如何实现封装

DAX 的封装，通过度量值完成。度量值依赖于数据模型，或者准确的说，是某个必然存在的数据模型中的字段，以确保某度量值是密闭的，不会发生变化的。同时，由于 Power BI Desktop 的软件特性，当度量值依赖的字段名称变化时，度量值会自动更新，用户无需维护。从这个意义上来说：

• 度量值可以设计成封闭的，以封装逻辑。
• 度量值一旦封闭，永久不变。其依赖发生变化后，对度量值的维护由系统自动完成。

因此，DAX 的度量值支持用封装的思想进行设计。

例如：

KPI.Value.Base = 

SUM( Fact_Sales[Value] )

这里定义的度量值 KPI.Value.Base 就是一种封装，它不会发生改变。

DAX 如何实现继承

DAX 的继承，通过度量值完成。例如：

KPI.Value = 

[KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend]

可以看到，度量值支持链式计算，从某种角度来说，如果遵循一定的设计原则，那么，下游度量值可以继承上游（依赖的）度量值的计算逻辑。

也就是说，DAX 的继承，更强调了一种业务逻辑的继承，这也是自然的，合理的。

不难想象，可以设计这样一个业务逻辑继承链路，如下：

• 基本指标值，如：求和。
• 继承后汇率转换，如：RMB，USD。
• 继承后单位转换，如：K，M，B。
• 继承后按时间计算，如：按 MTD，按 YTD 选项计算。

当用户在使用 KPI.Value.MTD 这个度量值的时候，其本质上，正在使用：

按 MTD 筛选出的交易按汇率转换后并以 K 做为单位计算后的结果。

以上这句话说得相当拗口，但它应该让你感受到这种设计的魅力所在。

?提示 小白进入到以 Power BI 作为泛型的自助商业智能分析领域，对技术的初步不适就是来自于这种逻辑的强大统一和输出，因为，这种逻辑是内涵式的，在 Excel 中，往往每一步就显性化地摆在那里，用户的大脑和眼睛可以看到每一步的结果；而在 DAX 中的计算，是被强大而清晰又优美的逻辑链条设计出来的，这种结合算力，逻辑，脑力为一体的全新体验，要真正感受到它的魅力后，就再也回不去了。

DAX 如何实现多态

多态性，是实现可复用以及动态执行的最关键特性。

先不去纠结其技术含义，来看这样一个例子：

问：KPI.Value 表示什么？

答：表示，啊，，，，没有说表示什么啊，，，

对了，就是这种感觉，可以看出：

KPI.Value 是一种抽象的字面含义，Value 既不是销售额，也不是销量，但它既可以是销售额，也可以是销量，但到底是什么呢？

如果给用户提供一个切片器，用户选了 “销售额”，则计算出销售额；用户选了 “销量”，则计算出销量。

因此，我们可以感受到：

KPI.Value 具有可变的多种含义，这种可变的含义，取决于用户的选择，这种计算并不发生在定义它的时候，而是发生在用户选择以后。这种随着环境动态可变而又可以被提前设计出来的机制，就是：多态性（Polymorphism）。

?插曲 多态性，是出现在计算机专业，编程中的专业术语，大概在科班中的大学二年级会学到，但在那时可以准确理解这个概念的学生并不多。直到大家未来从事了编程开发的工作，才能真正理解。但在这里，你已经理解了这个问题的内涵。

从这个意义上说，很多小伙伴问，DAX 到底强在什么地方，那么，你现在就可以感受到 DAX 可以支持设计出一种：

• 提前设计但又不运行
• 用户做了选择
• 根据选择再执行

这是一种倒置的结构。

还记得这个场景吗：

IT：你要什么？

业务：我要看销售额。

IT：什么的销售额。

业务：嗯，，，都有可能。

IT：那不行，你必须先说要看什么，才可能帮你查什么

业务：也有道理啊，但的确什么都有可能啊。

IT：那做不了的啊。

业务：那就看 XXXXX 吧。

如果你是业务，你现在就可以明白，你要的东西的确可以根据未来的可能而动态给出的。不能实现这种业务的诉求的 IT，实则是没有选择带有多态性机制的商业智能工具；

如果你是 IT，你现在就可以明白，并非现有需求，才有答案；需求是可以在可变的空间内，全部准备好的，等需求变了，结果自然改变。

多态性，如此重要，在数据分析中，你可能接触过很多技术工具，但能在这一层面轻松设计出动态可变具有多态性的技术解决方案的机制，DAX 天生如此。

从这个意义上来说，我们选择支持多形性的自助商业智能分析工具，DAX 具备这个特点，其他的，可以自行评估。

封装继承多态

再来看这个经典场景，如下：

设计这样一个业务逻辑继承链路，如下：

• 封装：基本指标值，如：求和。
• 继承：继承后汇率转换，如：RMB，USD。
• 继承：继承后单位转换，如：K，M，B。
• 多态：继承后按时间计算，如：按 MTD，按 YTD 选项计算。

可见，在实际中，不是一个个特性单独使用，而往往是一连串一起用，这种优美，你体会到了吗？

汇率转换设计模式

有了封装继承多态，我们就可以更好更本质地思考和进行设计了。以汇率转换为例，可以做出这样的设计：

KPI.Value.ByCurrency = 

// 意图将原始值转换为统一货币汇率值，再转为目标汇率货币值。

[KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend]

KPI.Value.ByCurrency 封装了一种不变的逻辑结构，就是：

交易转换为统一货币再转出为目标货币。

仔细思考，由于每天的汇率是不同的，在考虑汇率计算的时候，具体逻辑应该是：

• 先计算某天的统一值，再对所有日期的值求和。
  • 按当日的不同汇率分别计算统一值，再求和。
  • 为了计算某天的统一值，就需要：

不难看出，这里出现了一个二重循环迭代的逻辑。

这说明两件事：

• 第一，并不能直接适配 [KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend] 这样的计算框架。
  • 要考虑更内部的迭代。
• 第二，需要构建二重迭代结构来适配计算。

为此，将上述设计调整为：

KPI.Value.ByCurrency =SUMX(    VALUES('Calendar'[Date] ) ,    SUMX(        VALUES( Fact_Sales[CurrencyId] ) ,  
        [KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend]
    )
)

其中，两个嵌套的 SUMX 实现了二重迭代。

在最内层的迭代，计算基本聚合转为通用货币再转为目标货币的过程。

具体的实现，后文再做介绍。

数据模型设计

数据模型的设计，就像一种棋局，如何搭建数据模型，会都后续设计产生影响，主要包括：

• 应对不断变化的需求
• 提供高性能的支持

本案例中的数据模型采用了如下设计：

具备这样的好处：

• 只看右边，相当于没有汇率的干扰，与普通数据模型一致。
• 只看左边，反映了汇率以及随日期变化的记录。

汇率是事实表吗

汇率表具有的结构如下：

从某种角度来看，汇率每天都需要进行记录，所以是汇率的记录，这符合事实表的定义，因此，是事实表。但另一方面，除非用户去分析汇率的走势，否则，在没有分析意图下，汇率是一种参考查找的作用。

从使用的目的去界定事实表，维度表，可以启发设计师看到某表时应该怎样思考，具有很好的作用。在这里，汇率的目的不是事实，而是参考，因此，不作为事实表看待。将其命名为：Ref_Currency 可以体现使用它的目的。

汇率维度要和交易连接吗

观察这里的数据模型，可以看出，汇率定义维度，并未与交易事实表连接，实际的设计，也可以进行连接。这里不进行连接的设计思路是：

使用汇率的场景是很单一的，因此，尽量将这个部分隔离在主体数据模型之外，以凸显要分析的主体部分，而弱化辅助部分对主体部分的侵入。

这是：非侵入式设计的典型思维。（《PBI - 高级》课程中详细讲解了非侵入式设计，这里就不再重复）

再来观赏这个结构设计，如下：

汇率完全被边缘化到主体数据模型之外，仅仅复用主体数据模型的时间维度，这是合理的。

这样，设计师就是不受汇率的干扰，聚精会神继续考虑如何设计交易的相关分析。

?提示 数据模型的设计不是唯一的，但好的数据模型，可以极度降低后续思考设计的难度，这是数据建模的精妙和魅力所在。从一定意义上来看，如果设计师在后续的工作中，得益于一开始有意义的数据模型设计，会有一种显著的成就感，因为这种预判就是下棋的布局，虽然未见胜负，但整个棋局却了然于胸。

查找汇率

既然汇率维度没有侵入到主体数据模型，那么，对于每笔交易级别的汇率，必然要查找对应的汇率。设计如下：

Currency.LookUpValue = 

VAR vCurrentDate = MAX( 'Calendar'[Date] ) // 最近日期VAR vResult = // 最近一天有汇率的那天的汇率    CALCULATE( 
        LASTNONBLANKVALUE( 'Calendar'[Date] , SUM( Ref_Currency[RateValue] ) ) ,
        'Calendar'[Date] <= vCurrentDate
    )RETURN
    vResult

查找汇率采用度量值给出，基于考虑如下：

• 选定汇率维度的某个值，查找汇率。
• 度量值可以复用。

业务逻辑是：查找与汇率维度选择一致且小于等于该日期的最后可用汇率。

汇率转换设计模式的实现

考虑到汇率转换设计模式，如下：

为此，将上述设计调整为：

KPI.Value.ByCurrency =SUMX(    VALUES('Calendar'[Date] ) ,    SUMX(        VALUES( Fact_Sales[CurrencyId] ) ,  
        [KPI.Value.Base] * [Currency.To.Normal] * [Currency.To.Extend]
    )
)

考虑到在最内层迭代已经存在：

• 具体的日期
• 具体的汇率货币
• 查找汇率计算方法

则以上模式可以具体化为：

KPI.Value.ByCurrency.RawToNormalToExtend = 

// 构建二重迭代，对于每一天分别计算，其中，对每种汇率转换分别计算SUMX(    VALUES('Calendar'[Date] ) ,    SUMX(        VALUES( Fact_Sales[CurrencyId] ) ,  
        [KPI.Value.Base] * 
        COALESCE( CALCULATE( [Currency.LookUpValue] , TREATAS( { [CurrencyId] } , Dim_Currency[Index] ) ) , 1 ) /
        COALESCE( CALCULATE( [Currency.LookUpValue] , TREATAS( { SELECTEDVALUE( Option_Currency[Index] , 0 ) } , Dim_Currency[Index] ) ) , 1 )
    )
)

这里使用的技巧是：

• 将某日某货币的值
• 乘以转换系数转为统一货币
• 再除以转换系数转为目标货币

其中，用到了一个新的参数表：Option_Currency。它是用户的选择，如果用户选择了目标货币，则转出；如果用户没有选择，则保持统一货币。

这里的命名 RawToNormalToExtend 启发含义为原始值转为统一货币值再转为目标货币值。

其中，DAX 函数 COALESCE 实现查找不到汇率时，默认返回 1 以表示这是不需要找转换汇率的本币。

实现效果

按照这些的设计，实现了通用的计算效果，如下：

其含义为：

• 汇率记录：不同日期的汇率记录，可能有缺失日期，也需要满足。
• 汇率查找：在任何日期查找不同货币对本币（RMB）的汇率。
• 汇率计算：按照用户选择的输出型货币，在不同日期计算原始值（无意义），统一为本币（RMB）的值以及转出货币的值。

预计算以性能优化

关于什么时候使用度量值和计算列有很多争论，然而，理论上是不需要计算列的。计算列存在的一个真正重要意义，就是预计算。预计算，就是预先进行计算，将计算的结果存放起来，以便后续使用。

多态性与预计算

很多人不曾理解预计算的意义，但现在理解了。

很多人曾理解预计算的意义，但现在将更加透彻。

前文内容讲述了 “多态性”。可以发现：

预计算与多态性，就是天平的两端。

从多态性的角度：

• 度量值，保存最完整的多态性，但性能降低。
• 预计算，保存最快的聚合速度，但失去多态。

因此，

倾向于多态性，则应该使用度量值。

倾向于高性能，则应该尽量预计算。

但需要注意的是：

某些需求由于必须借助度量值的链式传导来描述其计算逻辑，是无法预计算的。

也就是说，

在某些场景下，同一问题，可以用度量值或计算列解决，没有什么差别。

在某些场景下，必须用度量值，是因为必须要保证多态性。

在某些场景下，由于性能极度降低，需要牺牲多态性来预计算。

具体的例子就不列举了，随着对数据模型使用的深入，会体会到上述三点是一种平衡，权衡，以及深度实践。

汇率统一预计算

由于在汇率计算场景下，任何一笔交易，都已经成为历史，在交易表中，是可以通过计算列先来将交易值参考汇率表，转换到统一货币的。如下：

ValueByCurrencyNormal = 
[Value] *
COALESCE(    CALCULATE(
        [Currency.LookUpValue] ,        TREATAS( { [Date] } ,'Calendar'[Date] ) ,        TREATAS( { [CurrencyId] } , Dim_Currency[Index] ) ,        ALL( )
    ) ,
    1
)

在交易表中新建计算列，并定义以上逻辑，即可完成预计算。

?提示 在计算列中使用度量值或 CALCULATE 应该注意防止上下文转换的副作用。这里运用了这个技巧。

这样就可以得到一个更加快速的度量值，如下：

KPI.Value.ByCurrency.Normal = 

// 原始聚合计算SUM( Fact_Sales[ValueByCurrencyNormal] )

优化计算

原有汇率计算的公式可以优化如下：

KPI.Value.ByCurrency.NormalToExtend = 

// 针对每种汇率货币，转换为通用货币SUMX(    VALUES( 'Calendar'[Date] ) ,    SUMX( 
        VALUES( Fact_Sales[CurrencyId] ) ,  
        [KPI.Value.ByCurrency.Normal] /
        COALESCE( 
            CALCULATE( 
                [Currency.LookUpValue] , 
                TREATAS( { SELECTEDVALUE( Option_Currency[Index] , 0 ) } , Dim_Currency[Index] ) 
            )
            , 
            1 
        )
    )
)

这里借助了已经预计算的值，再计算转出结果，减少了一个步骤，理论上会提升性能。

实际上，经过测试，这样的设计带来的性能优化是显著的。

进一步多态化

考虑到用户的选择以及度量值的有效性，用户可能选择，也可能不选择，综上，可以封装出一个度量值，如下：

KPI.Value.ByCurrency = 

IF( ISFILTERED( Option_Currency[Currency] ) ,
    [KPI.Value.ByCurrency.NormalToExtend] ,
    [KPI.Value.ByCurrency.Normal]
)

这样就得到了一个通用的度量值，可以适配于任何场景。

适配性

使用日期或更高粒度的计算时，这个模式也可以确保正确的计算。如下：

可以看出，选择了不同的日期，在不同时间的粒度，都可以确保计算正确。

上述具备了多态性的度量值 KPI.Value.ByCurrency 可以自动适配这些变化。

同理可以推测，在施加了各种时间维度计算后，仍然有效。

总结

本文给出了汇率转换的通用方案。该通用方案，已经几乎考虑了最基本的抽象，并可以适配几乎任何情况。同时，给出了性能优化后的版本。

更重要的是，本文解释了高级设计背后的思想，这些思想是自然和简单的。如果技巧让人不断的记忆，那么思想则让人不断的享受。因为，每次设计都是同一思想的不断使用，重复。

本文同时揭示和抽象了 DAX 以及数据模型设计中蕴含的封装，继承，多态性质以及如何将这些性质用于设计的实际案例。