出处： https://www.powerbigeek.com/tableau-top-15-lod-expression/
仅练习pbi部分
1. 客户购买频率 Customer order frequency
写法1
1）构建频次表
PurchasingTimes = GENERATESERIES(1,20,1)
image.png
2）写dax，将频次表放在横坐标上，dax作为值
dax有两种写法
--视图层写法
Times =
//求每个次数有多少人
//先求每个人的次数(订单的非重复计数），然后按次数count人
VAR tb =
ADDCOLUMNS ( VALUES ( SuperStore[Customer ID] ), "times1", [orders] )
VAR a =
CALCULATE (
DISTINCTCOUNT ( SuperStore[Customer ID] ),
FILTER ( tb, [times1] = SELECTEDVALUE ( '1 purchase time'[Value] ) )
)
RETURN
a
--模型层写法
CustomerNum =
COUNTROWS (
FILTER (
VALUES ( SuperStore[Customer ID] ),
CALCULATE ( DISTINCTCOUNT ( SuperStore[Order ID] ) ) = SELECTEDVALUE ( '1 purchase time'[Value] )
)
)
image.png
写法2
频次区间
1）构建频次区间表
输入数据，构建如下区间
image.png
2）写dax，将频次表放在横坐标上，dax作为值
同样2种写法
--视图层写法
Times.2.2 =
//构建顾客+次数表格，然后过滤计数
VAR min_ =
SELECTEDVALUE ( '2 purchase time'[min] )
VAR max_ =
SELECTEDVALUE ( '2 purchase time'[max] )
VAR tb =
ADDCOLUMNS ( VALUES ( SuperStore[Customer ID] ), "times3", [orders] )
VAR result =
CALCULATE ( COUNTROWS ( FILTER ( tb, [times3] >= min_ && [times3] <= max_ ) ) )
RETURN
result
--模型层写法
Times.2 =
//filter过滤顾客，countrow计数； 过滤时，大于等于最小值，小于等于最大值
VAR min_ =
SELECTEDVALUE ( '2 purchase time'[min] )
VAR max_ =
SELECTEDVALUE ( '2 purchase time'[max] )
VAR tb_customer =
CALCULATETABLE (
VALUES ( SuperStore[Customer ID] ),
FILTER (
VALUES ( SuperStore[Customer ID] ),
[orders] >= min_
&& [orders] <= max_
)
)
VAR result =
COUNTROWS ( tb_customer )
RETURN
result
image.png
