125_Power BI 中 DAX 的性能测试

博客：www.jiaopengzi.com

焦棚子的文章目录

 请点击下载附件

一、背景

最近在看DAX“圣经”第二版《The Definitive Guide to DAX (2nd ed.)》的时候，迭代函数并不慢。所以今天我们来用实验来验证一把。

二、实验过程

1、一个1000万的数据，数据好看出差异。

2、把度量写好。

//1、基础度量

total = SUM(data[NUM]) 

//2、SC


_sum_calculate =


CALCULATE ( SUM ( data[NUM] ), 'dim'[DIM] = "A", 'data'[ID] < 50000001 )

//3、SCV


_sum_calculatetable_var =


VAR T1 =


CALCULATETABLE ( 'dim', 'dim'[DIM] = "A" )


VAR T2 =


CALCULATETABLE ( 'data', 'data'[NUM] < 5000001 )


VAR V =


CALCULATE ( SUM ( data[NUM] ), T1, T2 )


RETURN


V

//4、SF


_sum_filter =


CALCULATE (


SUM ( data[NUM] ),


FILTER ( 'dim', 'dim'[DIM] = "A" ),


FILTER ( 'data', 'data'[NUM] < 5000001 )


)

//5、SFV


_sum_filter_var =


VAR T1 =


FILTER ( 'dim', 'dim'[DIM] = "A" )


VAR T2 =


FILTER ( 'data', 'data'[NUM] < 5000001 )


VAR V =


CALCULATE ( SUM ( 'data'[NUM] ), T1, T2 )


RETURN


V

//6、TC


_total_calculate =


CALCULATE ( 'measure'[total], 'dim'[DIM] = "A", 'data'[ID] < 50000001 )

//7、TCV


_total_calculatetable_var =


VAR T1 =


CALCULATETABLE ( 'dim', 'dim'[DIM] = "A" )


VAR T2 =


CALCULATETABLE ( 'data', 'data'[NUM] < 5000001 )


VAR V =


CALCULATE ( 'measure'[total], T1, T2 )


RETURN


V

//8、TF


_total_filter =


CALCULATE (


'measure'[total],


FILTER ( 'dim', 'dim'[DIM] = "A" ),


FILTER ( 'data', 'data'[NUM] < 5000001 )


)



//9、TFV


_total_filter_var =


VAR T1 =


FILTER ( 'dim', 'dim'[DIM] = "A" )


VAR T2 =


FILTER ( 'data', 'data'[NUM] < 5000001 )


VAR V =


CALCULATE ( 'measure'[total], T1, T2 )


RETURN


V