Powerbi 实战 15道练习题（1）客户购买频率

出处： 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