Excel¸ïÃü£¡³¬¼¶Êý¾Ý͸ÊÓ±íPower PivotÓëÊý¾Ý·ÖÎö±í´ïʽDAX¿ìËÙÈëÃÅ

µÚ1 Õ Power Pivot£¬³¬¼¶Êý¾Ý͸ÊÓ±í 1
1£®1 ´«Í³Excel Êý¾Ý͸ÊÓ±íµÄÄÜÁ¦Óë¾ÖÏÞ 1
1£®1£®1 ´«Í³Excel Êý¾Ý͸ÊÓ±íµÄÄÜÁ¦ 1
1£®1£®2 ´«Í³Excel Êý¾Ý͸ÊÓ±íµÄ¾ÖÏÞ 4
1£®2 Power Pivot ÔÚÊý¾Ý·ÖÎö·½ÃæµÄÓÅÊÆ 4
1£®2£®1 ¶à±í¹ØÁªÄÜÁ¦ 4
1£®2£®2 ¹¦Äܸü¼Ó·á¸» 7
1£®2£®3 ¸ü¿ìµÄÔËËãËÙ¶È 8
1£®3 Power Pivot£¬Êý¾Ý·ÖÎö¸üÖÇÄÜ 8

µÚ2 Õ Power Pivot£¬µ¥±í²Ù×÷ 10
2£®1 ´«Í³Excel Êý¾Ý͸ÊÓ±íµÄ¹¤×÷Ô­Àí £®£® 10
2£®2 Power Pivot£¬Excel ¸ïÃü £® 16
2£®3 ½øÈëPower Pivot ¹ÜÀí½çÃ森 19
2£®3£®1 ´ÓPivot Table µ½Power Pivot 19
2£®3£®2 Power Pivot ÖеÄ×Ô¶¨ÒåÔËËã·½·¨ £® 23
2£®3£®3 Power Pivot ÖеļÆËãÁÐÓë¶ÈÁ¿Öµ±í´ïʽ £® 25
2£®3£®4 *ÖØÒªµÄº¯Êý¨D¨DCALCULATE() º¯Êý 26
2£®4 Power Pivot ÓëDAX º¯Êý 29
2£®4£®1 ɸѡÏÞÖÆÒƳýº¯Êý¨D¨DALL() º¯Êý 30
2£®4£®2 ALL() º¯ÊýÓëALLEXCEPT() º¯Êý 36
2£®4£®3 CONCATENATEX() º¯ÊýÓëVALUES() º¯Êý £® 37
2£®4£®4 ɸѡº¯Êý¨D¨DFILTER() º¯Êý 40
2£®4£®5 CALCULATE() º¯ÊýÓëFILTER() º¯Êý 43
2£®4£®6 DAX ±í´ïʽÓëPower Pivot ³¬¼¶Êý¾Ý͸ÊÓ±í²¼¾Ö48
2£®4£®7 ¹ØÓÚCALCULATE() º¯ÊýµÄÀà±È 50
2£®4£®8 ·µ»Ø±íµÄCALCULATETABLE() º¯Êý 52
2£®4£®9 ÖðÐд¦Àí»ã×ܺ¯ÊýSUMX() 55
2£®5 Power Pivot µÄ³õ²½×ܽá 59

µÚ3 Õ Power Pivot£¬¶à±í½¨Ä£ 61
3£®1 Power Pivot Êý¾ÝÄ£Ð͵Ľ¨Á¢61
3£®1£®1 Êý¾ÝµÄ»ñÈ¡ 61
3£®1£®2 ʹÓÃPower Pivot µÄÊý¾Ý½¨Ä£ÄÜÁ¦ 62
3£®1£®3 Ò»»¨Ò»ÊÀ½ç£¬Ò»±íÒ»Ö÷Ìâ 63
3£®1£®4 ±íÖеĹؼü×ÖÓë·ÇÖظ´Öµ 66
3£®1£®5 ±í¼äµÄ¹ØÁª¹Øϵ 67
3£®1£®6 ÎÞÊý¾ÝÄ£ÐÍ£¬²»Power Pivot 71
3£®1£®7 ±í¼äµÄÉÏϼ¶¹Øϵ 73
3£®1£®8 ÓÃDAX ˼¿¼£¬Think in DAX 73
3£®2 ¶à±íÊý¾ÝÄ£ÐÍÖеļÆËãÁÐ 76
3£®2£®1 RELATED() º¯ÊýÓëRELATEDTABLE() º¯Êý 77
3£®2£®2 ×Ö·û´®Á¬½Óº¯ÊýCONCATENATEX() 83
3£®3 ¶à±íÊý¾ÝÄ£ÐÍÖеĶÈÁ¿Öµ±í´ïʽ 85
3£®3£®1 ¼ÆËãÁй«Ê½Óë¶ÈÁ¿Öµ±í´ïʽÔÚÓÃ;ÉϵÄÇø±ð 85
3£®3£®2 ½«CONCATENATEX() º¯ÊýÓÃÓÚ¶ÈÁ¿Öµ±í´ïʽÖÐ 87
3£®4 VALUES() º¯ÊýÓëDISTINCT() º¯Êý88
3£®4£®1 VALUES() º¯Êý £® 88
3£®4£®2 DISTINCT() º¯Êý £® 90
3£®5 ±í¼äɸѡ¡¢±íÄÚɸѡÓëALL() º¯Êý £® 91
3£®6 CALCULATE() º¯ÊýÓëCALCULATETABLE() º¯Êý £® 97
3£®7 ÖðÐд¦Àíº¯Êý¨D¨DSUMX() º¯ÊýÓëRANKX() º¯Êý 100
3£®7£®1 SUMX() º¯ÊýµÄ½øÒ»²½Ñо¿ 100
3£®7£®2 SUMX() º¯ÊýÓëRELATEDTABLE() º¯Êý £® 107
3£®7£®3 ÒÔX ½áβµÄÖðÐд¦Àíº¯ÊýµÄÌصã 108
3£®7£®4 Óеã¶ù²»Ò»ÑùµÄRANKX() º¯Êý £® 114
3£®8 DAX ±í´ïʽÓëPower Pivot Êý¾ÝÄ£ÐÍÃܲ»¿É·Ö £® 119
3£®8£®1 µ±Ç°±í¡¢Éϼ¶±íÓëϼ¶±í 121
3£®8£®2 DAX ±í´ïʽÓëÊý¾ÝÄ£ÐÍ £®£® 123
3£®8£®3 ɸѡµÄÊÇÁУ¬¿ØÖƵÄÊDZí 126

µÚ4 Õ ¼¸¸öÖØÒªµÄDAX º¯ÊýÔÙ̽ÌÖ 129
4£®1 DAX ºËÐĺ¯Êý¨D¨DCALCULATE() º¯Êý 129
4£®1£®1 ¹ØÓÚCALCULATE() º¯ÊýµÄÒ»¸öÖØÒªÊÂʵ 130
4£®1£®2 CALCULATE() º¯ÊýµÄ»ù±¾ÄÜÁ¦ £® 132
4£®1£®3 ¼ÆËãÁй«Ê½ÖеÄCALULATE() º¯Êý 134
4£®1£®4 CALCULATE() º¯ÊýµÄÓ¦Óó¡¾°×ܽá 139
4£®2 FILTER() º¯ÊýÓëCALCULATE() º¯Êý 139
4£®3 ¶à±íÄ£ÐÍÖеÄALL() º¯Êý 142
4£®3£®1 ALL() º¯ÊýµÄ²ÎÊýÊDZíÖеÄÒ»ÁÐ £® 143
4£®3£®2 ALL() º¯ÊýµÄ²ÎÊýÊÇÒ»¸ö±í 146
4£®3£®3 ALL() ²ÎÊýÊÇÒ»¸ö±íÖеĶàÁÐ 147
4£®3£®4 Power Pivot ¶à±íÊý¾ÝÄ£ÐÍÖйØÁª×ֶεÄɸѡЧ¹û £® 150
4£®3£®5 Power Pivot ¶à±íÊý¾ÝÄ£ÐÍÖеÄALL() º¯Êý £® 153
4£®4 Ö±½ÓɸѡºÍ½»²æɸѡ 157
4£®4£®1 Ö±½ÓɸѡÅж¨º¯ÊýISFILTERED() £® 157
4£®4£®2 ½»²æɸѡÅж¨º¯ÊýISCROSSFILTERED() £® 157
4£®5 µ¥Ò»ÖµÅжϺ¯ÊýHASONEVALUE() £® 162
4£®6 ɸѡµþ¼Óº¯ÊýKEEPFILTERS() 164
4£®7 ÓеãÄѶȵÄALLSELECTED() º¯Êý £®£® 167
4£®8 ALLSELECTED() º¯ÊýµÄÓ¦Óà 172
4£®9 VAR£¬¸´ÔÓDAX ·Ö²½Íê³É £® 173

µÚ5 Õ ÈÕÆÚ±íÓëÈÕÆÚÖÇÄܺ¯Êý £® 180
5£®1 ÈÕÆÚ±í£º±ê¼ÇÓë×Ô¶¯Éú³É 181
5£®2 ÓëÈÕÆÚÏà¹ØµÄ¡°ÖÇÄÜ¡±º¯Êý185
5£®2£®1 SAMEPERIODLASTYEAR() º¯Êý £® 185
5£®2£®2 DATESYTD() º¯Êý 189
5£®2£®3 TOTALYTD() º¯Êý 190

µÚ6 Õ DAX ÖеÄһЩÖØÒª¸ÅÄîÓ뺯Êý £® 192
6£®1 Power Pivot ÖеÄÊý¾ÝÀàÐÍ 192
6£®2 ÖðÐд¦Àíº¯ÊýÔÙ̽ÌÖ 194
6£®3 CALCULATE() º¯ÊýÔÙ»Ø¹Ë 199
6£®4 ´´½¨Power Pivot Êý¾ÝÄ£ÐÍÖеÄÁÙʱά¶È±í£® 202
6£®5 USERELATIONSHIP() º¯Êý 205
6£®6 EARLIER() º¯Êý £® 209

µÚ7 Õ DAX ·ÖÎö½á¹ûµÄ±í³ÊÏÖ 214
7£®1 ²éѯÇóÖµÖ¸ÁîEVALUATE £®£® 214
7£®2 ÓÃÓÚ±íÔÙÔìµÄº¯Êý 219
7£®2£®1 ·Ö×é»ã×ܺ¯ÊýSUMMARIZE() £® 219
7£®2£®2 Ôö¼Ó¼ÆËãÁк¯ÊýADDCOLUMNS() 224
7£®2£®3 ¹¹ÔìÐÂ±íº¯ÊýSELECTCOLUMNS() 226
7£®2£®4 Éú³ÉÖ»ÓÐÒ»ÐеıíµÄº¯ÊýROW() £®£® 228
7£®2£®5 ÔÚEVALUATE Ö¸ÁîÖÐʹÓöÈÁ¿Öµ±í´ïʽ £® 229
7£®3 ¼¸¸ö·ÂSQL ²éѯ¹¦ÄܵÄDAX º¯Êý 230
7£®3£®1 ½»¼¯²éѯº¯ÊýINTERSECT() £® 230
7£®3£®2 ½»²æÁ¬½Óº¯ÊýCROSSJOIN() £® 232
7£®3£®3 ½«Á½¸ö±í×ö¼õ·¨µÄº¯ÊýEXCEPT() 233
7£®3£®4 Á¬½Ó±íº¯ÊýUNION() 234
7£®3£®5 IN ²Ù×÷·ûÓëCONTAINSROW() º¯Êý 235

µÚ8 Õ Power Pivot ÓëDAX µÄ×ۺϰ¸Àý £® 241
ºó¼Ç 264