PowerBI中动态处理梯度类型的多条件判断

这个例子是我实际工作中需要，但方法不是我原创，我在大牛代码基础上夹带了一丁点私货做了一点点改进而已。

需求

先来看需求：如下图，订单数量居于不同区间获得不同数额的提成。而梯度划分和每个区间的提成数额都会发生变化，要求能快捷更新。

梯度

如果梯度和各区间提成数额都是固定的，很好办。但是如果梯度表要动态变化，就比较头大。

解决方案

我本来想用DAX来做，没搞定；转而求其次用PowerQuery来处理。

首先，如果梯度表要动态更新，那么它必须成为数据源表，而不能写死在公式里。

其次，既然梯度表是数据源，而又需要根据不同订单量从其每一行提取数据，那么这里就需要一个函数来动态取值。这个函数如下：

(单数,fn) => 
  let 提成区间 = Expression.Evaluate("{" & fn &"}"), 
  Result = Number.From(List.First(List.Select(提成区间, each _{0}(单数))){1})*(单数) 
in 
  Result

“提成区间”这一行，本来应该是这样子：

let
  提成区间=
   { 
        {(x)=>x<40, 0}, 
        {(x)=>x<50, 40}, 
        {(x)=>x<60, 60}, 
        {(x)=>x<70, 70}, 
        {(x)=>x<80, 80}, 
        {(x)=>x<95, 90}, 
        {(x)=>true, 100} 
    }, 

但是梯度表本身会发生变化，所以不能像上面那样写死。爬网之后，找到了利用Expression.Evaluate()来根据源表数据构造计算表达式的方法，这样源表数据发生变化，表达式也就跟着变化了。

为什么要用{}构造一个list？因为“Result=……”那一行就是从list中取数的。
为什么要用{(x)=>x<40, 0}这样的形式而不是{(单数)=>单数<40, 0}呢？其实这个问题我也问过，当时没想清楚，现在想清楚了——因为(x)=>x<40是一个嵌套函数，如果采用和主函数一样的变量“单数”，反而容易让人犯晕，用(x)=>x<40这样的方式就不会弄混淆。当然，如果确实要用{(单数)=>单数<40, 0}这样的，也是没问题的。因为函数的变量名只要遵循PowerQuery的规范，就不会有问题。

那为什么用这样的方式就能保证循环没有遗漏呢？这个我也没搞懂。但是我估计和梯度区间设置有关系——设置区间要保证任意一个数只能同时落在一个区间内，否则就会出错。

那么，“fn”是个什么东西？这个是我自己夹带的私货——因为我有无数个梯度表，这些梯度表最后都要利用Expression.Evaluate()生成计算表达式，我偷懒就用一个参数代替了，这样我就只需要一个函数，要不然我每个梯度表都要生成一个函数，而每个函数结构又是一模一样，那样就不“优雅”了。

接下来看

  Result = Number.From(List.First(List.Select(提成区间, each _{0}(单数))){1})*(单数)

each _{0}后面跟一个括号括起来的参数是什么意思呢？“ _{0}”的结果是提成区间那个list中每个子元素的第一行，这个第一行是什么呢？是类似于“(x)=>x<40”这样的函数，既然是函数就要有参数，这个函数的参数是“单数”，因此“ _{0}”后面跟了个“（单数）”。

接下来的重点是如何构造Expression.Evaluate()所需的计算表达式。这就需要对梯度源表进行改造。 改造后的梯度表

就是新增了初值和终值两列。这样当把改造后的梯度表引入到PowerBI中后，用下面的代码生成计算表达式：

let
源 = Excel.Workbook(File.Contents("D:\Path\提成系数.xlsx"), null, true),
筛选的行 = Table.SelectRows(源, each ([Kind] = "Table")),
选择表格 = 筛选的行{[Item="大区经理新客户上线提成",Kind="Table"]}[Data],
更改的类型 = Table.TransformColumnTypes(选择表格,{{"梯度", type text}, {"初值", Int64.Type}, {"终值", Int64.Type}, {"每单提成", Int64.Type}}),
计算规则 = Table.AddColumn(更改的类型, "计算规则", each if [终值]=null  then true else "<="&Text.From([终值])),
自定义1 = Table.AddColumn(计算规则, "构造表达式", each "{(x)=>x"&Text.From([计算规则])&", """&Text.From([每单提成])&"""}"),
替换的值 = Table.ReplaceValue(自定义1,"xtrue","true",Replacer.ReplaceText,{"构造表达式"}),
自定义2 = Text.Combine(替换的值[构造表达式], ",")
in
自定义2

前面说了(x)=>x可以用“(单数）=>单数”代替，结果是一样的。最后结果就是把表格变成了一个文本串，这个文本串可以直接放到Expression.Evaluate()的参数中，进行运算，得到结果，供下一步处理。

反思

目前采用PowerQuery解决，用DAX是不是更简单些呢，我估计如果梯度是写死的，用DAX估计会简单些。但考虑到梯度是动态变化的，需要变成数据源表，估计DAX处理起来就不如PowerQuery了。但具体结果如何，还有待试验了才知道。