PowerBI用的语法是DAX语法,独有的函数形式
用赋值法(VAR)会使其和python语法有些类似,而且易于学习

为什么Var在PowerBI里会比较好用

以下是一些常用函数的公式及写法

1.赋值计算昨日的值

S-Yest =
VAR Yesterday = DATEADD(LASTDATE(表[时间列]),-1,DAY)
VAR Yesterdaygmv = CALCULATE(SUM(表[gmv]),FILTER('日历','日历'[时间列]= Yesterday))
return Yesterdaygmv

2.进阶计算预测型-以CPC为例

S-todayCPC预测 (取点预测)= 
VAR Yesterday = DATEADD(LASTDATE(表[时间列]),-1,DAY)
VAR LastWeek = DATEADD(LASTDATE(表[时间列]),-7,DAY)
VAR LastMonth = DATEADD(LASTDATE(表[时间列]),-1,Month)
VAR LastYear = DATEADD(LASTDATE(表[时间列]),-1,Year)
VAR YesterdayCPC = DIVIDE(CALCULATE(SUM(表[Spending]),FILTER('日历','日历'[时间列]= Yesterday)),
                          CALCULATE(SUM(表[Click]),FILTER('日历'','日历'[时间列]= Yesterday)))
VAR LastWeekCPC = DIVIDE(CALCULATE(SUM(表[Spending]),FILTER('日历','日历'[时间列]= LastWeek)),
                          CALCULATE(SUM(表[Click]),FILTER('日历','日历'[时间列]= LastWeek)))
VAR LastMonthCPC = DIVIDE(CALCULATE(SUM(表[Spending]),FILTER('日历','日历'[时间列]= LastMonth)),
                          CALCULATE(SUM(表[Click]),FILTER('日历','日历'[时间列]= LastMonth)))
VAR LastYearCPC = DIVIDE(CALCULATE(SUM(表[Spending]),FILTER('日历','日历'[时间列]= LastYear)),
                          CALCULATE(SUM(表[Click]),FILTER('日历','日历'[时间列]= LastYear)))
RETURN
DIVIDE((YesterdayCPC+LastWeekCPC+LastMonthCPC+LastYearCPC),
      (IF(ISBLANK(YesterdayCPC),0,1)
        +IF(ISBLANK(LastWeekCPC),0,1)
        +IF(ISBLANK(LastMonthCPC),0,1)
        +IF(ISBLANK(LastYearCPC),0,1)))
Last modification:August 15th, 2018 at 12:41 pm
性感博主,在线要饭