µÚ3ÕÂ

²éѯ±í





DAX¾ßÓÐÁ½´óÖ÷Òª¹¦ÄÜ£º ²éѯÓë¼ÆËã(¼ÆËãÁÐÓë¶ÈÁ¿Öµ)¡£DAXÀûÓòéѯµÄɸѡ¡¢·Ö×éºÍ»ã×ܹ¦ÄÜÀ´¼õÉÙ¿ÉÄܽϴóµÄÊý¾ÝÁ¿ºÍ½Ï¸´ÔÓµÄÄ£ÐÍ£¬ÀàËÆÓÚSQL²éѯµÄ¹¦ÄÜ¡£Excel Power PivotÖеIJéѯ±íÊÇͨ¹ýEVALUATEÉùÃ÷À´Íê³ÉµÄ¡£ÔÚPower BIÖд´½¨²éѯ±í¸üΪ¼ò½Ý£¬¿ÉÖ±½Ó²ÉÓá°Ð½¨±í¡±Íê³É¡£

3.1±íº¯Êý
3.1.1ͳ¼Æѧ»ù´¡

ÔÚ¸ÅÂÊÓëÊýÀíͳ¼ÆÖУ¬ÓÐÔªËØ¡¢¼¯ºÏ¡¢¶ÔÏó¡¢Ê¼þµÈ¸ÅÄî¡£¼¯ºÏÓÉÔªËØ×é³É£¬×é³É¼¯ºÏµÄÿ¸ö¶ÔÏó±»³ÆΪ×é³É¸Ã¼¯ºÏµÄÔªËØ¡£¼¯ºÏÄÚµÄÔªËØ¿ÉΪÊýÖµÐÍ¡¢Îı¾ÐÍ»òÆäËûÀàÐÍ£¬Ã¿¸ö¼¯ºÏÖÁÉÙ°üº¬Á½¸ö×Ó¼¯£º ¸Ã¼¯ºÏ±¾ÉíºÍ¿ÕÖµ¡£¿Õ¼¯ÓëÁ±íʾ£¬È«¼¯ÓÃU±íʾ¡£

¼¯ºÏÓëʼþ¼äµÄ¹Øϵ·ÖΪ°üº¬(A¡ÊB)¡¢½»(A¡ÉB)¡¢²¢(A¡ÈB)¡¢»¥³â¡¢¶ÔÁ¢¡¢²î(A-B)¼¸ÖÖ¡£

»¥³âʼþÓë¶ÔÁ¢Ê¼þµÄÇø±ð£º »¥³âʼþÖеÄA¡¢B²»¿ÉÄÜͬʱ·¢Éú£¬Ò²¿ÉÒÔ¶¼²»·¢Éú£¬»¥³â¼¯µÄA¡ÉB=«Á¡¢A¡ÈB=A+B¡£¶ÔÁ¢Ê¼þÖеÄA¡¢B²»¿ÉÄÜͬʱ·¢Éú£¬µ«±ØÐëÓÐÒ»¸ö·¢Éú£¬¶ÔÁ¢¼¯µÄA¡ÉB=«Á¡¢A¡ÈB=U¡£¶ÔÁ¢Ê¼þ¿Ï¶¨Îª»¥³âʼþ£¬¶ø»¥³âʼþ²»Ò»¶¨ÊǶÔÁ¢Ê¼þ¡£

ÒÔÏÂÊÇͳ¼ÆѧÖеÄһЩ»ù±¾¹«Ê½¡£

1. ¼¯ºÏÓëÔªËØ


x¡ÊA»òAÿðþ
½•x(3ª²1)

ʽ(3ª²1)ÖУº x(Сд)ÊǼ¯ºÏÖеÄÒ»¸öÔªËØ£¬A(´óд)ÊǼ¯ºÏ¡£x¡ÊAÏ൱ÓÚ¼ÆËã»úÓïÑÔÖеÄx in A£» Aÿðþ
½•xÏ൱ÓÚ¼ÆËã»úÓïÑÔÖÐ
µÄA contains x¡£¶þÕß´ú±íµÄÒâÒåÊÇÏàͬµÄ¡£

xªüA»òAÿðþ
½–x(3ª²2)

ʽ(3ª²2)ÖУº xªüAÏ൱ÓÚx not in A£» Aÿðþ
½–xÏ൱ÓÚA not contains x¡£¶þÕß´ú±íµÄÒâÒåÊÇÏàͬµÄ¡£

2. ¼¯ºÏÓëʼþ


A¡ÈB(3ª²3)

ʽ(3ª²3)ÖУº ¼¯ºÏ¾ß±¸È·¶¨ÐÔ¡¢»¥ÒìÐÔ¡¢ÎÞÐòÐÔÕâ3¸öÌØÐÔ¡£»¥ÒìÐÔÊÇָͬһ¸ö¼¯ºÏÖеÄÔªËØÊÇ»¥²»ÏàͬµÄ£¬Òò¶øA¡ÈB= A+B-A¡ÉB¡£

ªÐX¡ÊA:X(3ª²4)

ʽ(3ª²4)ÖУº ªÐÊǸø¶¨¼¯ºÏµÄÕûÌ壬X´ú±íµÄÊÇ×Ó¼¯(subset)£¬A´ú±íµÄÊǼ¯ºÏ(set)£¬Ã°ºÅ(£º)´ú±íµÄÊǸø¶¨¼¯ºÏ¡£ÀýÈç¡°ªÐX¡ÊA:XÊÇżÊý¡±´ú±íµÄÒâÒåΪ¡°A¼¯ºÏ¡¢X×Ó¼¯ÖеÄËùÓÐżÊý¡±¡£

3.1.2ALL()

µ±Éæ¼°µ¥±íÊý¾ÝÑÝʾʱ£¬±¾ÊéÖ÷ÒªÓõ½µÄÊý¾ÝÔ´(¹¤×÷²¾Ãû³ÆDOCK.xlsx£¬±íÃû³ÆDK)¼û±í3ª²1¡£

±í3ª²1±íÃû³Æ(DK)



ÈÕÆÚ²úÆ·°ü×°·½Ê½Èë¿â³ö¿â¶ÖÊý·½ÊýµÈ¼¶

2020/9/2¸Ö»¯Ä¤É¢×°763518.8656A
2020/9/27µ°¸âÖ½Ïä×°321221.2785A
2020/11/2ÓÍÆáÍ°×°552320.8972B
2021/9/29À¦873321.22C
2021/9/29¾»»¯¼ÁÍ°×°934220.8959B
2021/10/11Æ»¹û´×Ïä×°783665B
2021/11/14°ü×°ÉþÔú653222.8969A


ÔÚDAXÖУ¬ALL()º¯ÊýÖ÷ÒªÓÐÁ½´ó¹¦ÄÜ£º ÒýÓÃ(·µ»Ø±í)¼°µ÷½ÚÆ÷(ÒƳýɸѡ)£¬¸Ãº¯Êý¿´ËƼòµ¥µ«¹¦ÄÜÇ¿´óÈ´ÓÖ²»ºÃÀí½â£¬ËüÊÇDAXÖÐÒ»¸öÏ൱ÖØÒªµÄº¯Êý£¬Óï·¨ÈçÏ£º 



ALL(£Û<table> | <column>£Û, <column>£Û, <column>£Û,¡­£Ý£Ý£Ý£Ý )





˵Ã÷£º ALL()º¯Êý²»µ¥¶ÀʹÓ㬳£ÓÃ×÷FILTER()¡¢CALCULATE()µÈº¯ÊýµÄ²ÎÊý¡£ALL()ÔÚFILTER()º¯ÊýÖÐÓÃÓÚÒýÓñí»òÁв¢·µ»Ø±í¡£ÔÚCALCULATE()º¯ÊýÖÐÓÃÓÚ¸ü¸ÄÖ´ÐйýÆäËû¼ÆËãµÄ½á¹û¼¯£¬·µ»Ø±íÖеÄËùÓÐÐлòÁÐÖеÄËùÓÐÖµ¡£ºöÂÔ¿ÉÄÜÒÑÓ¦ÓõÄÈκÎɸѡÆ÷¡£

ALL()º¯ÊýÓÃÓÚ·µ»Ø±íµÄËùÓÐÖµ£¬±í´ïʽÈçÏ£º 



EVALUATE   //ch3-001,EVALUATE¶Ô´óСд²»Ãô¸Ð

ALL('DK') //¸´ÖƱí,¸Ã±í´ïʽÓë'DK'µÈЧ¡£



-- ÀàËÆÓÚ΢ÈíOLEDBÖеÄSQLÓï¾äSELECT * FROM £ÛDK$£Ý

-- ˵Ã÷£º ±í´ïʽÖÐ"--""//"¾ùΪDAXÖеĵ¥ÐÐ×¢ÊÍ·û£¬¶þÕß¹¦ÄÜÉÏÎÞÇø±ð





·µ»ØµÄÖµÊÇÍêÕûµÄDK±íÊý¾Ý¡£

×¢Ò⣺ µ±Êý¾ÝÁ¿¹ý´óʱ£¬Ó¦¾¡Á¿±ÜÃâʹÓÃALL()ÒýÓø´ÖÆ»òÒýÓÃÕû¸ö±í£¬ÌرðÊǸ´ÖÆ»òÒýÓÃÕû¸öµÄλÓÚÊý¾ÝÄ£ÐͶà¶ËµÄÊý¾ÝÃ÷ϸ±í£¬Ëü»áʹÔËÐÐЧÂʵÍÏ¡£

All()º¯Êý²»½ö¿ÉÒÔÒýÓÃ±í£¬»¹¿ÉÒÔÒýÓÃÁУ¬ËüÃǵÄÓ÷¨ÊÇÒ»ÑùµÄ£¬±í´ïʽÈçÏ£º 



EVALUATE  //ch3-002

ALL('DK'£Û°ü×°·½Ê½£Ý) //¶ÔÁÐÖеÄÖµ½øÐÐÈ¥ÖØ



-- ÀàËÆÓÚ΢ÈíOLEDBÖеÄSQLÓï¾äSELECT DISTINCT °ü×°·½Ê½ FROM £ÛDK$£Ý





·µ»ØµÄÖµÈçͼ3ª²1Ëùʾ¡£

×¢Ò⣺ ALL()º¯ÊýÄܹ»Ê¹ÓÃÁÐʱ¾¡Á¿²»Ê¹ÓÃ±í£¬ALL('±í' £ÛÁУÝ)·½Ê½¿É·µ»ØÁеIJ»Öظ´Öµ£¬ÔÚÊý¾ÝÁ¿¹ý´óʱ£¬´Ë²Ù×÷¿É¼«´óµØÌáÉýЧÂÊ¡£

ALL()º¯Êý¿É½ÓÊܵ¥Áлò¶àÁÐ×÷Ϊ²ÎÊý£¬All()º¯ÊýËùÓÐÒýÓÃÁвÎÊý±ØÐëÀ´×ÔͬһÕÅ±í£¬±í´ïʽÈçÏ£º 



EVALUATE   //ch3-003

ALL('DK'£Û°ü×°·½Ê½£Ý ,'DK'£Û²úÆ·£Ý )



-- ÀàËÆÓÚ΢ÈíOLEDBÖеÄSQLÓï¾äSELECT DISTINCT °ü×°·½Ê½,²úÆ· FROM £ÛDK$£Ý





·µ»ØµÄÖµÈçͼ3ª²2Ëùʾ¡£



ͼ3ª²1µ¥ÁÐÒýÓÃ




ͼ3ª²2¶àÁÐÒýÓÃ



ALL()µÄÈ¥Öع¦ÄÜÓ¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



M.ÒýÓÃ:=COUNTROWS(ALL(DK))		¡¡¡¡//7



M.È¥ÖØ:=COUNTROWS(ALL(DK£Û°ü×°·½Ê½£Ý,DK£ÛµÈ¼¶£Ý))¡¡¡¡//6





ÔÚDK±íµÄ7ÐÐÊý¾ÝÖУ¬ÓÐÁ½ÐÐÍ°×°µÈ¼¶ÎªBµÄÊý¾Ý£¬È¥ÖغóµÄÐÐÊýΪ6¡£

ALL()º¯ÊýÔÚFILTER()ÖÐʹÓÃʱÊÇ±íº¯Êý£¬ÓÃÓÚ±íµÄÒýÓûò±íÁеÄɾ³ýÖظ´Ï·µ»ØµÄÖµ»áÒƳýÍⲿɸѡÉÏÏÂÎÄ¡£Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



M.ÐÐÊýA:= COUNTROWS(ALL('DK'))



M.ÐÐÊýAF:= COUNTROWS( FILTER(ALL('DK'), 'DK'£Û°ü×°·½Ê½£Ý="Ïä×°")





ALL()ÓÃÓÚÀ©Õ¹ÐÐÊý£¬FILTER()ÓÃÓÚËõ¼õÐÐÊý£¬·µ»ØµÄÖµÈçͼ3ª²3Ëùʾ¡£



ͼ3ª²3ÒƳýɸѡ


3.1.3VALUES()

VALUES()º¯ÊýÓÃÓÚ·µ»ØÔÚµ±Ç°É¸Ñ¡Æ÷ÖмÆËãµÄÁеIJ»Í¬Öµ£¬¸Ãº¯ÊýÒ²ÊÇDAXÖÐÒ»¸öÏ൱ÖØÒªµÄÇÒÒ×ÓëALL()»ìÏýµÄº¯Êý£¬Óï·¨ÈçÏ£º 



VALUES(<TableNameOrColumnName>)





VALUES()º¯Êýɾ³ýÖظ´Ïî·µ»ØΨһֵ£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-004

VALUES('DK'£Û°ü×°·½Ê½£Ý )





·µ»ØµÄÖµÈçͼ3ª²4Ëùʾ¡£

VALUES()º¯ÊýÖ»½ÓÊܵ¥ÁÐ×÷Ϊ²ÎÊý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-005

VALUES('DK'£Û°ü×°·½Ê½£Ý ,'DK'£Û²úÆ·£Ý )





ÒÔÉϲéѯÒýÓÃÁ˶þÁÐ×÷Ϊ²ÎÊý£¬·µ»ØµÄ´íÎóÌáʾÈçͼ3ª²5Ëùʾ¡£



ͼ3ª²4µ¥ÁÐÒýÓÃ




ͼ3ª²5´íÎóÌáʾ



´´½¨¶ÈÁ¿ÖµM.ÐÐÊýVºÍM.ÐÐÊýVF£¬±í´ïʽÈçÏ£º 



M.ÐÐÊýV:=COUNTROWS(VALUES('DK'))



M.ÐÐÊýVF:=COUNTROWS( FILTER(VALUES('DK'),'DK'£Û²úÆ·£Ý="µ°¸âÖ½"))





VALUES()ÓëALL()·µ»ØµÄÖµ¶Ô±ÈÈçͼ3ª²6Ëùʾ¡£



ͼ3ª²6¶Ô±È˵Ã÷


×¢Ò⣺ ÔÚ¶ÈÁ¿ÖµÖУ¬ALL()º¯ÊýÓÃÓÚÒƳýɸѡ£¬VALUES()º¯ÊýÓÃÓÚ±£³ÖÍⲿɸѡÉÏÏÂÎIJ¢·µ»ØÔÚµ±Ç°É¸Ñ¡Æ÷ÖмÆËãÁеIJ»Í¬Öµ£¬ALL()ÓëVALUES()ÔÚɸѡµÄ¹¦ÄÜÉÏÊÇÏà·´µÄ¡£

3.2¸ß¼¶É¸Ñ¡

FILTER()º¯ÊýÊÇÒ»¸ö»ùÓÚÌõ¼þ±í´ïʽµÄ±íº¯Êý¡£Ëü»á¸ù¾ÝµÚ2¸ö²ÎÊýµÄɸѡÌõ¼þÀ´É¸Ñ¡µÚ1¸ö²ÎÊýµÄ±í£¬·µ»ØµÄÊÇÒ»¸ö±í¡£ÆäɸѡÄÜÁ¦Ô¶´óÓÚCALCULATE()º¯ÊýµÄÖ±½Óɸѡ·½Ê½£¬¿ÉÊÊÓÃÓÚ¸÷ÀิÔÓɸѡÌõ¼þ£¬µ«ÆäÔËÐÐЧÂʲ»ÈçCALCULATE()µÄÖ±½Óɸѡ¡£FILTER()º¯ÊýÓÃÓÚ»ñÈ¡ÍⲿɸѡÌõ¼þÏÂµÄ±í£¬Æä·µ»ØֵΪ±í£¬Óï·¨ÈçÏ£º 



FILTER(<table>,<filter>)





˵Ã÷£º FILTER()º¯ÊýÊǵü´úº¯Êý£¬ÆäÔËÐÐЧÂÊÔ¶²»ÈçCALCULATE()º¯Êý¡£µ±CALCULATE()º¯ÊýµÄÖ±½ÓɸѡÄÜÂú×ãɸѡҪÇóʱ£¬¾¡Á¿²»ÒªÓÃFILTER()º¯Êý£» Ö»Óе±CALCULATE()º¯Êý²»ÄÜÂú×ãɸѡҪÇóʱ£¬ÔÙʹÓÃCALCULATE()+FILTER()µÄǶÌ×Ó÷¨¡£ÔÚʹÓÃFILTER()º¯Êýʱ£¬FILTER()º¯ÊýÓ¦¾¡Á¿·ÅÔÚ(À´×ÔÄ£ÐÍÖеÄÒ»¶ËµÄ)²éѯ±íÖÐʹÓã¬ÕâÑù¿ÉÒÔÌáÉýÔËÐÐЧÂÊ¡£

3.2.1ɸѡӦÓÃ

ÔÚDAXÖУ¬FILTER()ÊÇÒ»¸öʹÓÃƵÂʺܸߵĺ¯Êý¡£³öÓÚÌáÉýЧÂʵĿ¼ÂÇ£¬ÔÚʹÓÃɸѡº¯ÊýFILTER()Ç°Ðë½÷¼ÇÁ½¸öÔ­Ôò£º ¢ÙÄܹ»ÓÃCALCULATE()º¯ÊýµÄÖ±½ÓÌõ¼þ½â¾öµÄ¾Í¾¡Á¿²»ÒªÓÃFILTER()£» ¢ÚʹÓÃFILTER()º¯Êýʱ£¬µÚ1¸ö²ÎÊýÄÜÖ¸¶¨¾ßÌåÁеľ;¡Á¿²»ÒªÓÃÕû¸ö±í¡£

ALL()º¯Êý¡¢DISTINCT()º¯Êý¡¢VALUES()º¯Êý³£ÓÃÓÚFILTER()º¯ÊýµÄµÚ1¸ö²ÎÊý¡£ÒÔALL()º¯ÊýÔÚFILTER()ÖÐ×÷ɸѡ²ÎÊýµÄÓ¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-006

FILTER ( ALL ( 'DK'£Û°ü×°·½Ê½£Ý ), 'DK'£Û°ü×°·½Ê½£Ý = "É¢×°" )



-- ÀàËÆÓÚ΢ÈíOLEDB SQLÓï¾äSELECT DISTINCT °ü×°·½Ê½ FROM £ÛDK$£Ý WHERE °ü×°·½Ê½="É¢×°"





·µ»ØµÄÖµÈçͼ3ª²7Ëùʾ¡£

ÔÚ²éѯµÄ¹ý³ÌÖÐ˳±ã¶ÔÖ¸¶¨×ֶνøÐÐÅÅÐò£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-007

FILTER ( ALL ( 'DK'£Û°ü×°·½Ê½£Ý, 'DK'£Û¶ÖÊý£Ý ),'DK'£Û¶ÖÊý£Ý >21 )

ORDER BY 'DK'£Û¶ÖÊý£Ý DESC



-- ÀàËÆÓÚOLEDB SQLÓï¾äSELECT DISTINCT °ü×°·½Ê½,¶ÖÊý FROM £ÛDK$£Ý WHERE ¶ÖÊý>21 

-- ORDER BY ¶ÖÊý DESC





·µ»ØµÄÖµÈçͼ3ª²8Ëùʾ¡£



ͼ3ª²7¹Ì¶¨ÖµÉ¸Ñ¡




ͼ3ª²8Ìõ¼þɸѡ



FILTER()º¯ÊýµÄµÚ2¸ö²ÎÊýΪÌõ¼þ±í´ïʽ£¬ÊÊÓÃÓÚ<ÁÐ>=£Û¶ÈÁ¿£Ý¡¢<ÁÐ>=(¹«Ê½)¡¢<ÁÐ>=<ÁÐ>¡¢£Û¶ÈÁ¿£Ý=£Û¶ÈÁ¿£Ý¡¢£Û¶ÈÁ¿£Ý=(¹«Ê½)¡¢£Û¶ÈÁ¿£Ý=¹Ì¶¨ÖµµÈ±È½Ï·½Ê½¡£ÒÔÉϱí´ïʽÖеÄ=(µÈºÅ)Ò²¿ÉÒÔÊÇ< >¡¢>¡¢>=¡¢<¡¢<=µÈÆäËû±È½ÏÔËËã·û¡£FILTER()º¯ÊýµÄÔËËãÌõ¼þ»¹¿ÉÒÔΪ&&¡¢¡¬¡¢IN¡¢NOTµÈÂß¼­ÔËËã·û¡£

1. µ¥Ìõ¼þ

ÒÔ<ÁÐ>=¹Ì¶¨ÖµÎªÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-008

FILTER ( 'DK', 'DK'£Û¶ÖÊý£Ý >21 )



-- ÀàËÆÓÚ΢ÈíOLEDBÖеÄSQLÓï¾äSELECT * FROM £ÛDK$£Ý WHERE ¶ÖÊý>6





·µ»ØµÄÖµÈçͼ3ª²9Ëùʾ¡£



ͼ3ª²9µ¥Ìõ¼þɸѡ


2. ¶àÌõ¼þ

Âß¼­ÔËËã·ûÓÐ&&(AND)¡¢¡¬(OR)¡¢IN¡¢NOT£¬Ïà¹Ø˵Ã÷²Î¼û±í1ª²8¡£ÒÔÂß¼­¹ØϵÓë(AND¡¢&&)ΪÀý£¬²ÉÓÃAND()д·¨£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-009

FILTER ( 'DK', AND('DK'£ÛÈë¿â£Ý >60, 'DK'£ÛÈë¿â£Ý > 'DK'£Û³ö¿â£Ý * 2.2 ))



-- ÀàËÆÓÚSQLÓï¾äSELECT * FROM £ÛDK$£Ý WHERE Èë¿â>60 AND Èë¿â>³ö¿â*2.2





²ÉÓÃ&&д·¨£¬±í´ïʽÈçÏ£º 



EVALUATE

FILTER ( 'DK', 'DK'£ÛÈë¿â£Ý >60&& 'DK'£ÛÈë¿â£Ý > 'DK'£Û³ö¿â£Ý * 2.2 )





ÒÔÉÏÁ½ÖÖ±í´ïʽ·µ»ØµÄÖµÈçͼ3ª²10Ëùʾ¡£



ͼ3ª²10¶àÌõ¼þɸѡ


3.2.2×ÛºÏÓ¦ÓÃ

FILTER()»ùÓÚµÚ1¸ö²ÎÊýÀ´Ñ¡Ôñ±í»òÁУ¬»ùÓÚµÚ2¸ö²ÎÊýÖеÄÌõ¼þ±í´ïʽʵÏÖ¶Ô±íÖÐÐеÄɸѡ¡£FILTER()µÄµÚ1¸ö²ÎÊý¿ÉÒÔÊǾßÌåµÄ±í»ò±íº¯ÊýµÄ·µ»ØÖµ£¬ÀýÈçALL()º¯Êý¡¢FILTER()¡¢SUMMARIZE()µÈ±íº¯ÊýµÄ·µ»ØÖµ£¬´Ó¶ø¹¹³É±íº¯ÊýµÄǶÌ×£¬µÚ2¸ö²ÎÊý¿ÉÒÔÊǸ÷ÀิÔÓµÄÂß¼­±í´ïʽ¡£

1. »ù´¡Ó¦ÓÃ

±íº¯ÊýµÄǶÌ×Ó¦Óã¬ALL()ÓÃÓÚÀ©Õ¹¼ÆËãµÄ·¶Î§¡¢FILTER()ÓÃÓÚ¼õÉÙ¼ÆËãµÄÐÐÊý¡£Í¨¹ýALL()º¯ÊýÒýÓÃÑ¡ÔñÈ¥ÖغóµÄÁÐ(°ü×°·½Ê½¡¢Èë¿â)£¬Í¨¹ýÌõ¼þ±í´ïʽÀ´Ñ¡Ôñ(ÓÉ°ü×°·½Ê½¡¢Èë¿â¶þÁй¹³ÉµÄ)бíÖзûºÏÌõ¼þµÄÐÐ(°ü×°·½Ê½ÎªÉ¢×°»òÏä×°)£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-010

FILTER ( 

ALL ( 'DK'£Û°ü×°·½Ê½£Ý, 'DK'£ÛÈë¿â£Ý ),

'DK'£Û°ü×°·½Ê½£Ý="É¢×°"||'DK'£Û°ü×°·½Ê½£Ý="Ïä×°"

)



-- ÀàËÆÓÚ΢ÈíOLEDBÖеÄSQLÓï¾äSELECT DISTINCT °ü×°·½Ê½,Èë¿â FROM £ÛDK$£Ý WHERE 

-- °ü×°·½Ê½="É¢×°" OR °ü×°·½Ê½="Ïä×°"





µ±FILTER()º¯ÊýµÄµÚ2¸ö²ÎÊýµÄÂß¼­±í´ïʽΪ»ò¹Øϵ(¡¬¡¢OR)ʱ£¬ºÜ¶àÇé¿öÏÂÒ²¿ÉÒÔ²ÉÓÃINµÄ·½Ê½£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-011

FILTER ( 

ALL ( 'DK'£Û°ü×°·½Ê½£Ý, 'DK'£ÛÈë¿â£Ý ), 

'DK'£Û°ü×°·½Ê½£Ý IN { "É¢×°", "Ïä×°" }//IN¶Ô´óСд²»Ãô¸Ð

)



-- ÀàËÆÓÚ΢ÈíOLEDBÖеÄSQLÓï¾äSELECT DISTINCT °ü×°·½Ê½,Èë¿âFROM £ÛDK$£ÝWHERE °ü×°·½Ê½

-- IN ("Ïä×°","É¢×°")





Èç¹û½«ÒÔÉÏINÔËËã·û»»³ÉCONTAINSROW()º¯Êý£¬Ôò¶ÔÓ¦µÄ±í´ïʽÈçÏ£º 



EVALUATE//ch3-012

FILTER ( ALL ( 'DK'£Û°ü×°·½Ê½£Ý, 'DK'£ÛÈë¿â£Ý ),

CONTAINSROW (

{ "É¢×°", "Ïä×°" },

'DK'£Û°ü×°·½Ê½£Ý

)

)







ͼ3ª²11FILTER()ÓëALL()
µÄ½»»¥(1)


ÒÔÉÏ3¸ö±í´ïʽ·µ»ØµÄÖµÊÇÏàͬµÄ£¬Èçͼ3ª²11Ëùʾ¡£

×ÛÉÏ£¬DAX²éѯÓï¾äÖеÄFILTER()º¯ÊýµÄµÚ2¸ö²ÎÊýÀàËÆÓÚSQLÓï¾äÖеÄWHEREÓ÷¨¡£±¾Õ²»ÔÙÒ»Ò»ÁоٶÔÓ¦µÄSQLÓï¾ä¡£



2. ½ø½×Ó¦ÓÃ

±íº¯ÊýµÄǶÌ×Ó¦ÓþÙÀý£¬ÒÔFILTER()ÓëFILTER()µÄǶÌ×ΪÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-013

FILTER (

FILTER ( ALL ( 'DK'£Û°ü×°·½Ê½£Ý, 'DK'£ÛÈë¿â£Ý ), 'DK'£Û°ü×°·½Ê½£Ý = "É¢×°" ),

'DK'£ÛÈë¿â£Ý > 60

) 





Á½¸öFILTER()µÄǶÌ×£¬Ï൱ÓÚÌõ¼þµÄ&&£¬ÒÔÉϱí´ïʽµÄµÈЧ±í´ïʽÓ÷¨ÈçÏ£º



EVALUATE//ch3-014

FILTER (

ALL ( 'DK'£Û°ü×°·½Ê½£Ý, 'DK'£ÛÈë¿â£Ý ),

'DK'£Û°ü×°·½Ê½£Ý = "É¢×°" && 'DK'£ÛÈë¿â£Ý > 60

) 





·µ»ØµÄÖµÈçͼ3ª²12Ëùʾ¡£

ÒÔFILTER()ÓëALL()µÄǶÌ×ΪÀý£¬µÚ2¸ö²ÎÊý²ÉÓÃINÂß¼­ÔËËã·û£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-015

FILTER (

ALL ( 'DK'£Û°ü×°·½Ê½£Ý, 'DK'£ÛÈë¿â£Ý, 'DK'£Û³ö¿â£Ý ),

'DK'£Û°ü×°·½Ê½£ÝIN { "É¢×°", "Ïä×°" }

&& 'DK'£Û³ö¿â£Ý >30

)

ORDER BY 

'DK'£Û°ü×°·½Ê½£Ý,'DK'£ÛÈë¿â£Ý ASC,

'DK'£Û³ö¿â£Ý DESC





·µ»ØµÄÖµÈçͼ3ª²13Ëùʾ¡£



ͼ3ª²12FILTER()ÓëALL()µÄ½»»¥(2)




ͼ3ª²13FILTER()ÓëALL()µÄ½»»¥(3)



½öÓÃÓÚ²éѯ»ò´´½¨±íʱ£¬ÒÔÏÂÁ½¸ö±í´ïʽËù·µ»ØµÄÖµÊÇÏàͬµÄ¡£Èç¹û½«Æä·ÅÔÚµü´úº¯Êý»òÕßCALCULATE()º¯ÊýÖУ¬ÔòALL()º¯ÊýÓÃÓÚÒƳýɸѡ£¬VALUES()º¯ÊýÓÃÓÚ±£³ÖÍⲿɸѡÉÏÏÂÎÄ¡£¶þÕߵķµ»ØÖµ½«»á´æÔڽϴóµÄ²îÒ죬Æä¾ßÌåµÄ²îÒìÐè·ÅÔÚ¶ÈÁ¿ÖµÖнáºÏÍⲿɸѡÉÏÏÂÎIJÅÄÜÕæÕýÌåÏÖ³öÀ´(±¾Õ²»½øÐÐÉîÈë˵Ã÷)¡£²éѯӦÓ㬱í´ïʽÈçÏ¡£ 

²éѯһ£º 



DEFINE MEASURE¡¡//ch3-016

'DK'£ÛM.Èë¿âÁ¿£Ý = SUM ( 'DK'£ÛÈë¿â£Ý )

EVALUATE

VAR A = SUM ( DK£ÛÈë¿â£Ý ) RETURN

FILTER ( ALL ( DK£Û°ü×°·½Ê½£Ý ), £ÛM.Èë¿âÁ¿£Ý <= A )	





²éѯ¶þ£º 



DEFINE MEASURE//ch3-017

'DK'£ÛM.Èë¿âÁ¿£Ý = SUM ( 'DK'£ÛÈë¿â£Ý )

EVALUATE

VAR A = SUM ( DK£ÛÈë¿â£Ý ) RETURN

FILTER ( VALUES ( DK£Û°ü×°·½Ê½£Ý ), £ÛM.Èë¿âÁ¿£Ý <= A )





²éѯһÓë²éѯ¶þËù·µ»ØµÄÖµÈçͼ3ª²14Ëùʾ¡£



ͼ3ª²14FILTER()ÓëALL()µÄ½»»¥(4)


3.3±íɸѡ

CALCULATETABLE()º¯ÊýÓëFILTER()º¯Êý·µ»ØµÄ¾ùÊÇɸѡºóµÄ±í£¬µ«¶þÕߵļÆËãÂß¼­ÊDz»Ò»ÑùµÄ£¬CALCULATETABLE()º¯ÊýÏÈÖ´ÐÐËùÓÐɸѡ²ÎÊý£¬È»ºóÖ´ÐеÚ1¸ö²ÎÊý£¬ÆäÏà¹ØÓï·¨ÈçÏ£º 



CALCULATETABLE(

<expression>£Û, 

<filter1> £Û, <filter2> £Û, ¡­£Ý£Ý£Ý

)





CALCULATETABLE()µÄɸѡ²ÎÊýΪÓÃÓÚ¶¨ÒåɸѡÆ÷(µ±´æÔÚ¶à¸öɸѡÆ÷ʱ£¬¿ÉʹÓÃÂß¼­ÔËËã·û&&¡¢¡¬¡¢IN¶ÔËüÃǽøÐмÆËã)»òɸѡµ÷½ÚÆ÷º¯Êý(ALL()¡¢ALLEXCEPT()¡¢ALLNOBLANKROW()¡¢KEEPFILTERS()¡¢REMOVEFILTERS()¡¢CROSSFILTER()¡¢USERELATIONSHIP())µÄ²¼¶û±í´ïʽ(ֵΪTRUE»òFALSE)»ò±í±í´ïʽ¡£¸Ãº¯ÊýµÄµÚ2¸ö²ÎÊýµÄÓ÷¨ÓëCALCULATE()µÄµÚ2¸ö²ÎÊýµÄÓ÷¨ÊÇÒ»Öµģ¬Æä²îÒìÔÚÓÚµÚ1¸ö²ÎÊý¡£

ÔÚCALCULATE()¼°CALCULATETABLE()º¯ÊýÖУ¬µ±ALL()º¯Êý×÷Ϊµ÷½ÚÆ÷ʹÓÃʱ£¬Æ书ÄÜÓëREMOVEFILTERS()º¯ÊýÊÇÍêÈ«Ò»Öµġ£

3.3.1ɸѡ»ù´¡

µ±É¸Ñ¡Ìõ¼þµÄ±í´ïʽΪ'±í'£ÛÁУÝ=¹Ì¶¨ÖµÊ±£¬CALCULATETABLE()º¯ÊýÓëFILTER()º¯ÊýµÄº¯Êý·µ»ØÖµ²»´æÔÚ²îÒ죬±í´ïʽÈçÏ£º 



EVALUATE//ch3-018

CALCULATETABLE('DK','DK'£Û°ü×°·½Ê½£Ý="Ïä×°")





ÒÔÉϱí´ïʽµÈЧÓÚFILTER()±í´ïʽµÄ±í´ïʽÈçÏ£º 



EVALUATE//ch3-019

FILTER('DK','DK'£Û°ü×°·½Ê½£Ý="Ïä×°")





·µ»ØµÄÖµÈçͼ3ª²15Ëùʾ¡£



ͼ3ª²15ɸѡµÄÌõ¼þΪ¹Ì¶¨Öµ


CALCULATE()¼°CALCULATETABLE()º¯ÊýµÄµÚ2¸ö²ÎÊýÖ»ÊÊÓÃÓÚΪ'±í'£ÛÁУÝ=¹Ì¶¨ÖµµÄÇéÐΡ£µ±´æÔÚ<ÁÐ>=£Û¶ÈÁ¿£Ý¡¢<ÁÐ>=(¹«Ê½)¡¢<ÁÐ>=<ÁÐ>¡¢£Û¶ÈÁ¿£Ý=£Û¶ÈÁ¿£Ý¡¢£Û¶ÈÁ¿£Ý=(¹«Ê½)µÄÇéÐÎʱ£¬µÚ2¸ö²ÎÊýµÄɸѡֻÄÜÓÃFILTER()º¯ÊýµÄÌõ¼þ±í´ïʽ£¬¾ÙÀýÈçÏ£º 



EVALUATE//ch3-020

FILTER ('DK', 'DK'£ÛÈë¿â£Ý>=AVERAGE('DK'£ÛÈë¿â£Ý))





·µ»ØµÄÖµÈçͼ3ª²16Ëùʾ¡£



ͼ3ª²16ɸѡµÄÌõ¼þΪ¹«Ê½


Èç¹û½«ÒÔÉÏ´úÂëÖеÄFILTER()º¯ÊýÌ滻ΪCALCULATETABLE()º¯Êý£¬Ôò±í´ïʽÈçÏ£º 



EVALUATE//ch3-021

CALCULATETABLE('DK', 'DK'£ÛÈë¿â£Ý>=AVERAGE('DK'£ÛÈë¿â£Ý))





·µ»ØµÄ´íÎóÌáʾÈçͼ3ª²17Ëùʾ¡£



ͼ3ª²17±¨´íÌáʾ


¶ÔÒÔÉϱí´ïʽ½øÐÐÐÞÕý£¬½«µÚ2¸ö²ÎÊýµÄÖ±½Ó±í´ïʽ»»³ÉFILTER()¸ß¼¶É¸Ñ¡£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-022

CALCULATETABLE (

'DK',

FILTER ('DK','DK'£ÛÈë¿â£Ý>= AVERAGE ( 'DK'£ÛÈë¿â£Ý ))

)





ÏÔʾÕý³££¬·µ»ØµÄÖµÈçͼ3ª²16Ëùʾ¡£

3.3.2ɸѡ˳Ðò

ÔÚDAXÖУ¬FILTER()º¯ÊýΪµü´úº¯Êý£¬µü´úº¯Êý²úÉúÐÐÉÏÏÂÎÄ£¬ÐÐÉÏÏÂÎÄÎÞ·¨×ª»»ÎªÉ¸Ñ¡ÉÏÏÂÎÄ£¬¶øCALCULATE()ºÍCALCULATETABLE()ÕâÁ½¸öº¯Êý¾ß±¸ÉÏÏÂÎÄת»»µÄÄÜÁ¦¡£FILTER()µÄÐÐÉÏÎÄÓ¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-023

FILTER(

ADDCOLUMNS (

VALUES ( 'DK'£Û°ü×°·½Ê½£Ý ),

"DKÊý", COUNTROWS ( 'DK' )

),

'DK'£Û°ü×°·½Ê½£Ý IN{"Í°×°","Ïä×°"}

)





ɸѡµÄ˳Ðò¼°·µ»ØµÄÖµÈçͼ3ª²18Ëùʾ¡£



ͼ3ª²18FILTER()µÄÓ¦ÓÃ


ͼ3ª²18ÖУ¬FILTER()º¯ÊýµÄÐÐÉÏÏÂÎÄ»á±éÀúÕû¸öDK±í£¬·µ»ØµÄֵΪDK±íµÄ×ÜÐÐÊý£¬·µ»ØµÄֵΪ·ÇÆÚÍûµÄÖµ¡£

¶ÔÒÔÉϱí´ïʽ½øÐÐÐÞÕý£¬½«ÒÔÉϱí´ïʽÉϵÄFILTER()¸ÄΪCALCULATETABLE()£¬Ð޸ĺóµÄ±í´ïʽÈçÏ£º 



EVALUATE//ch3-024

CALCULATETABLE(

ADDCOLUMNS (

VALUES ( 'DK'£Û°ü×°·½Ê½£Ý ),

"DKÊý", COUNTROWS ( 'DK' )

),

'DK'£Û°ü×°·½Ê½£Ý IN{"Í°×°","Ïä×°"}

)





ɸѡµÄ˳Ðò¼°·µ»ØµÄÖµÈçͼ3ª²19Ëùʾ¡£



ͼ3ª²19CALCULATETABLE()µÄÓ¦ÓÃ


CALCULATETABLE()º¯ÊýÔËÐÐʱÏÈɸѡÔÙÔËË㣬FILTER()º¯ÊýÔËÐÐʱÏÈÔËËãÔÙɸѡ¡£ÕâÊǶþÕßÔËËã˳ÐòµÄÇø±ð¡£Í¼3ª²19ÖУ¬CALCULATETABLE()º¯Êý·µ»ØµÄֵΪDK±íÖÐÍ°×°¡¢Ïä×°Êý¾ÝµÄ×ÜÐÐÊý£¬·µ»ØµÄÖµÈÔΪ·ÇÆÚÍûµÄÖµ¡£

´Ëʱ¿ÉÒÔ¶ÔÒÔÉϱí´ïʽ½øÐÐÐÞÕý£¬ÔÚCOUNTROWS ( 'DK' )ÍâÃæÌ×ÉÏCALCULATE()£¬²ÉÓÃFILTER()º¯Êý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-025

FILTER(

ADDCOLUMNS (

VALUES ( 'DK'£Û°ü×°·½Ê½£Ý ),

"DKÊý", CALCULATE(COUNTROWS ( 'DK' ))

),

'DK'£Û°ü×°·½Ê½£Ý in{"Í°×°","Ïä×°"}

)





»òÕß½«ÒÔÉϱí´ïʽÖеÄFILTER()¸ÄΪCALCULATETABLE()£¬Ð޸ĺóµÄ±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-026

CALCULATETABLE(

ADDCOLUMNS (

VALUES ( 'DK'£Û°ü×°·½Ê½£Ý ),

"DKÊý", CALCULATE(COUNTROWS ( 'DK' ))

),

'DK'£Û°ü×°·½Ê½£Ý in {"Í°×°","Ïä×°"}

)





ÒÔÉÏÁ½¸ö±í´ïʽµÄ·µ»ØÖµÏàͬ£¬Èçͼ3ª²20Ëùʾ¡£

ÔÚCALCULATETABLE()ÖУ¬¿ÉÔÚµÚ2¸ö²ÎÊýÖÐʹÓõ÷½ÚÆ÷º¯ÊýALL()ÒƳýɸѡ£¬±í´ïʽÈçÏ£º 



EVALUATE

CALCULATETABLE (

VALUES ( DK£Û²úÆ·£Ý ),//ÊÜɸѡӰÏìµÄÖµ

ALL ( DT£ÛÈÕÆÚ£Ý )//ÒƳýɸѡ£¬À©´óÉÏÏÂÎĵķ¶Î§

)





ÒÔÉϱí´ïʽÖÐALL()º¯ÊýµÄÊý¾Ý¿ÉÀ´×ÔÊÂʵ±í£¬Ò²¿ÉÀ´×Ô²éѯ±í£¬Ò»ÇÐÊÓ±í´ïʽµÄÐèÒª£¬·µ»ØµÄÖµÈçͼ3ª²21Ëùʾ¡£



ͼ3ª²20ÉÏÏÂÎĵÄת»»Ó¦ÓÃ




ͼ3ª²21ÉÏÏÂÎĵÄת»»Ó¦ÓÃ



3.3.3²îÒì±È½Ï

ÒÔÏÂÊÇFILTER()ÓëCALCULATETABLE()µÄһЩÆäËû²îÒì±È½Ï¡£

´´½¨²éѯ±í£¬±í´ïʽÈçÏ£º 



EVALUATE ¡¡¡¡//ch3-027

FILTER(

ALL ( 'DK'£Û²úÆ·£Ý ),

'DK'£Û°ü×°·½Ê½£Ý IN {"Í°×°","Ïä×°"}

)





·µ»ØµÄ´íÎóÌáʾÈçͼ3ª²22Ëùʾ¡£



ͼ3ª²22´íÎóÌáʾ


ÐÞ¸ÄÉÏÃæµÄ±í´ïʽ£¬½«FILTER()ÐÞ¸ÄΪCALCULATETABLE()£¬Ð޸ĺóµÄ±í´ïʽÈçÏ£º 



EVALUATE//ch3-028

CALCULATETABLE(

ALL ( 'DK'£Û²úÆ·£Ý ),

'DK'£Û°ü×°·½Ê½£Ý IN {"Í°×°","Ïä×°"}

)





ɸѡµÄÔ­Àí¼°·µ»ØµÄÖµÈçͼ3ª²23Ëùʾ¡£



ͼ3ª²23ɸѡ·µ»ØÖµ(1)


ͼ3ª²23ÖдæÔÚ¿ÕÖµ£¬ÇÒδ×öÈκεÄɸѡ¡£ÔÚÉÏÃæµÄ±í´ïʽÖУ¬ALL()ÊÇCALCULATETABLE()º¯ÊýµÄµ÷½ÚÆ÷º¯Êý£¬ÓÃÓÚÒƳýɸѡ¡£½«ÒÔÉϱí´ïʽÖеÄALL()ÐÞ¸ÄΪDISTINCT()£¬Ð޸ĺóµÄ±í´ïʽÈçÏ£º 



EVALUATE//ch3-029

CALCULATETABLE(

DISTINCT ( 'DK'£Û²úÆ·£Ý ),

'DK'£Û°ü×°·½Ê½£Ý IN {"Í°×°","Ïä×°"}

)





ɸѡµÄÔ­Àí¼°·µ»ØµÄÖµÈçͼ3ª²24Ëùʾ¡£



ͼ3ª²24ɸѡ·µ»ØÖµ(2)


ALL()¡¢VALUES()¡¢DISTINCT()µÄ±È½Ï˵Ã÷£º 

(1) ALL()º¯ÊýÓÃÓÚÒƳýɸѡ£¬VALUES()¼°DISTINCT()º¯ÊýÓÃÓÚ±£³ÖÍⲿɸѡÉÏÏÂÎIJ¢·µ»ØÔÚµ±Ç°É¸Ñ¡Æ÷ÖмÆËãÁеIJ»Í¬Öµ£¬ALL()ÓëVALUES()ÔÚɸѡµÄ¹¦ÄÜÉÏÊÇÏà·´µÄ¡£

(2) ALL()º¯Êý¡¢VALUES()º¯ÊýÊÓ¿ÕÐÐΪÓÐЧÐУ¬²¢½«ÆäÏÔʾ³öÀ´£¬ÕâÒ»µãÓëDISTINCT()¹¦ÄÜ´æÔÚ²îÒì¡£DISTINCT()»áÇå³ý¿ÕÐУ¬ÆäËû·½ÃæÓëVALUES()¹¦ÄÜ´óÌåÒ»Ö¡£

3.4¼¯ºÏÓëʼþ

³£¼ûµÄ¼¯ºÏÓëʼþµÄ¹ØϵÓв¢¼¯(UNION)¡¢½»¼¯(INTERSECT)¡¢²î¼¯(EXCEPT)¡£ÆäÁ¬½ÓµÄÁ½¸öÊý¾Ý¿ÉÀ´×ÔͬһÊý¾ÝÔ´£¬Ò²¿ÉÒÔÀ´×Ô²»Í¬µÄÊý¾ÝÔ´£¬Æä·µ»ØµÄÊý¾ÝÈçͼ3ª²25Ëùʾ¡£



ͼ3ª²25Êý¾ÝµÄ¼¯ºÏ


ÔÚ½»¼¯Óë²î¼¯ÖУ¬Èç¹û¼¯ºÏAÓ뼯ºÏBÔÚº¯Êý²ÎÊýÖеÄλÖò»Í¬£¬ÔòÆä·µ»ØµÄÖµÒ²½«²»Í¬¡£

3.4.1UNION()

UNION()º¯ÊýÓÃÓÚ´ÓÒ»¶Ô±í´´½¨ÁªºÏ(Á¬½Ó)±í£¬Óï·¨ÈçÏ£º 



UNION(<table_expression1>, <table_expression2>£Û,<table_expression>£Ý¡­)





Á¬½ÓµÄÁ½¸öÊý¾ÝÀ´×ÔͬһÊý¾ÝÔ´£¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-030

VAR T1 =

FILTER ( ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý >75 )

VAR T2 =

FILTER ( ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý <50 )

RETURN

UNION ( T1, T2 )

ORDER BY £ÛÈë¿â£Ý DESC





ÔËËã¹ý³Ì¼°·µ»ØµÄÖµÈçͼ3ª²26Ëùʾ¡£



ͼ3ª²26UNION()º¯ÊýµÄÓ¦ÓÃ(1)


Á¬½ÓµÄÁ½¸öÊý¾ÝÀ´×ÔͬһÊý¾ÝÄ£Ð͵IJ»Í¬±í¸ñ£¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-031

DISTINCT(  //ɾ³ýÁÐÖÐÖظ´ÏîÇÒÈ¥³ý¿ÕÖµ

UNION(	

VALUES('ºÏͬ'£Û²úÆ·£Ý), VALUES('¶©µ¥'£Û²úÆ·£Ý)//´Ë´¦µÄVALUES¿ÉÓÃDISTINCTÌæ»»

)

)//±íÃûµÄµ¥ÒýºÅ''²»ÄÜÉÙ¡£








ͼ3ª²27UNION()º¯Êý
µÄÓ¦ÓÃ(2)


·µ»ØµÄÖµÈçͼ3ª²27Ëùʾ¡£

3.4.2INTERSECT()

INTERSECT()º¯ÊýÓÃÓÚ·µ»ØÁ½¸ö±íµÄÐн»¼¯£¬±£ÁôÖظ´ÏÓï·¨ÈçÏ£º 



INTERSECT(<table_expression1>, <table_expression2>)





×¢Ò⣺ INTERSECT()º¯Êý±£ÁôµÄÊÇ×ó±íÊý¾ÝÑØÏ®¡£INTERSECT(T1, T2)ÓëINTERSECT (T2, T1)·µ»ØµÄÖµºÜ¶àÇé¿öÏ´æÔÚ²îÒì¡£

Á¬½ÓµÄÁ½¸öÊý¾ÝÀ´×ÔͬһÊý¾ÝÔ´£¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-032

VAR T1 =

FILTER ( ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý > 60 )

VAR T2 =

FILTER ( ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý <80 )

RETURN

INTERSECT( T1, T2 )

ORDER BY £ÛÈë¿â£Ý DESC





ÔËËã¹ý³Ì¼°·µ»ØµÄÖµÈçͼ3ª²28Ëùʾ¡£



ͼ3ª²28INTERSECT()º¯ÊýµÄÓ¦ÓÃ(1)


Á¬½ÓµÄÁ½¸öÊý¾ÝÀ´×ÔͬһÊý¾ÝÄ£Ð͵IJ»Í¬±í¸ñ£¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-033

DISTINCT(  INTERSECT(	VALUES('ºÏͬ'£Û²úÆ·£Ý), VALUES('¶©µ¥'£Û²úÆ·£Ý) ))







ͼ3ª²29INTERSECT()
º¯ÊýµÄÓ¦ÓÃ(2)


·µ»ØµÄÖµÈçͼ3ª²29Ëùʾ¡£

3.4.3EXCEPT()

EXCEPT()º¯ÊýÓÃÓÚ·µ»ØÒ»¸ö±íµÄÐС£ÕâЩÐгöÏÖÔÚµÚ1¸ö±í£¬µ«Î´³öÏÖÔÚµÚ2¸ö±í£¬Óï·¨ÈçÏ£º 



EXCEPT(<table_expression1>, <table_expression2>





EXCEPT()º¯Êý±£ÁôµÄÊÇ×ó±íÊý¾ÝµÄÑØÏ®¡£ÔÚEXCEPT()ÖУ¬¶ÔµÚ1¸öºÍµÚ2¸ö²ÎÊýµÄ±í½øÐÐλÖý»»»ºó½«·µ»Ø²»Í¬µÄÖµ¡£

Á¬½ÓµÄÁ½¸öÊý¾ÝÀ´×ÔͬһÊý¾ÝÔ´£¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-034

VAR T1 =

FILTER ( ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý > 60 )

VAR T2 =

FILTER ( ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý <80 )

RETURN

EXCEPT( T1, T2 )

ORDER BY £ÛÈë¿â£Ý DESC





ÔËËã¹ý³Ì¼°·µ»ØµÄÖµÈçͼ3ª²30Ëùʾ¡£



ͼ3ª²30EXCEPT()º¯ÊýµÄÓ¦ÓÃ(1)


Á¬½ÓµÄÁ½¸öÊý¾ÝÀ´×ÔͬһÊý¾ÝÄ£Ð͵IJ»Í¬±í¸ñ£¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-035

DISTINCT(  EXCEPT(VALUES('ºÏͬ'£Û²úÆ·£Ý), VALUES('¶©µ¥'£Û²úÆ·£Ý) ))





·µ»ØµÄÖµÈçͼ3ª²31Ëùʾ¡£



ͼ3ª²31EXCEPT()º¯ÊýµÄÓ¦ÓÃ(2)


3.5µÑ¿¨¶ù»ý
3.5.1CROSSJOIN()

CROSSJOIN()º¯ÊýÓÃÓÚ·µ»ØÒ»¸ö±í£¬ÆäÖаüº¬²ÎÊýÖÐËùÓбíµÄËùÓÐÐеĵѿ¨¶ù»ý¡£Ð±íÖеÄÁÐÊÇËùÓвÎÊý±íÖеÄËùÓÐÁУ¬Óï·¨ÈçÏ£º 



CROSSJOIN(<table>, <table>£Û, <table>£Ý¡­)





Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-036

VAR T1 = { "Ïä×°", "Í°×°" }

VAR T2 = { ( 93, 87, 78 ), ( 65, 55, 32 ) }

RETURN

CROSSJOIN ( T1, T2 )





·µ»ØµÄÖµÈçͼ3ª²32Ëùʾ¡£



ͼ3ª²32CROSSJOIN()º¯ÊýµÄÓ¦ÓÃ


3.5.2GENERATE()

GENERATE()º¯ÊýÓÃÓÚ·µ»ØÒ»¸ö±í£¬ÆäÖаüº¬table1ÖеÄÿÐÐÓëÔÚtable1µÄµ±Ç°ÐеÄÉÏÏÂÎÄÖмÆËãtable2ËùµÃ±íÖ®¼äµÄµÑ¿¨¶ù³Ë»ý£¬Óï·¨ÈçÏ£º 



GENERATE(<table1>, <table2>)





Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-037

GENERATE (

FILTER(VALUES('DK'£Û°ü×°·½Ê½£Ý), 'DK'£Û°ü×°·½Ê½£Ý IN { "É¢×°", "Ïä×°" } ) ,

FILTER(ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý > 80 ) )





·µ»ØµÄÖµÈçͼ3ª²33Ëùʾ¡£



ͼ3ª²33GENERATE()º¯ÊýµÄÓ¦ÓÃ(1)


×¢Ò⣺ table1ºÍtable2ÖеÄËùÓеÄÁÐÃû²»µÃÏàͬ£¬·ñÔò»á·µ»Ø´íÎó¡£

½«ÒÔÉϱí´ïʽ½øÐÐÐÞÕý£¬Ê¹Á½¸ö±íÖ®¼äµÄ²úÆ·ÁгöÏÖÖØÃû£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-038

GENERATE (

FILTER(VALUES('DK'£Û²úÆ·£Ý), 'DK'£Û°ü×°·½Ê½£Ý IN { "¾»»¯¼Á", "¸Ö»¯Ä¤" } ) ,

FILTER ( ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý > 80 ) )





ϵͳ±¨´íÌáʾÈçͼ3ª²34Ëùʾ¡£



ͼ3ª²34GENERATE()º¯ÊýµÄÓ¦ÓÃ(2)


3.5.3GENERATEALL()

GENERATEALL()º¯ÊýÓÃÓÚ·µ»ØÒ»¸ö±í£¬ÆäÖаüº¬table1ÖеÄÿÐÐÓëÔÚtable1µÄµ±Ç°ÐеÄÉÏÏÂÎÄÖмÆËãtable2ËùµÃ±íÖ®¼äµÄµÑ¿¨¶ù³Ë»ý£¬Óï·¨ÈçÏ£º 



GENERATEALL(<table1>, <table2>)





Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-039

GENERATEALL (

FILTER(VALUES('DK'£Û°ü×°·½Ê½£Ý), 'DK'£Û°ü×°·½Ê½£Ý IN { "É¢×°", "Ïä×°" } ) ,

FILTER(ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý > 80 ) )





·µ»ØµÄÖµÈçͼ3ª²35Ëùʾ¡£



ͼ3ª²35GENERATEALL()º¯ÊýµÄÓ¦ÓÃ


¶Ô±Èͼ3ª²34¼°Í¼3ª²35ºó¿É·¢ÏÖ£¬GENERATE()ÓëGENERATEALL()º¯ÊýµÄÇø±ðÔÚÓÚÊý¾ÝµÄ×éºÏ·½Ê½£¬Æä²îÒì¶Ô±È˵Ã÷Èçͼ3ª²36Ëùʾ¡£



ͼ3ª²36GENERATE()ÓëGENERATEALL()µÄ¶Ô±È˵Ã÷


3.6Á¬½Ó²éѯ
3.6.1NATURALINNERJOIN()

NATURALINNERJOIN()º¯ÊýÓÃÓÚÖ´ÐÐÒ»¸ö±íÓëÁíÒ»¸ö±íµÄÄÚ²¿Á¬½Ó¡£ÕâЩ±íÔÚÁ½¸ö±íµÄ¹²ÓÐÁÐ(°´Ãû³Æ)ÉÏÁ¬½Ó¡£Èç¹ûÁ½¸ö±íûÓй«¹²ÁÐÃû£¬Ôò·µ»Ø´íÎó£¬Óï·¨ÈçÏ£º 



NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)





NATURALINNERJOIN()º¯ÊýÀàËÆÓÚSQLÖеÄINNER JOIN¡£Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-040

VAR T1 =

FILTER ( 








ALL('DK'£Û²úÆ·£Ý, 'DK'£Û°ü×°·½Ê½£Ý,'DK'£ÛÈë¿â£Ý), 

'DK'£ÛÈë¿â£Ý > 70 

)

VAR T2 =

FILTER ( 

ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 

'DK'£ÛÈë¿â£Ý < 90 

)

RETURN 

NATURALINNERJOIN(T1,T2) 





·µ»ØµÄÖµÈçͼ3ª²37Ëùʾ¡£



ͼ3ª²37NATURALINNERJOIN()º¯ÊýµÄÓ¦ÓÃ


3.6.2NATURALLEFTOUTERJOIN()

NATURALLEFTOUTERJOIN()ÓÃÓÚÖ´ÐÐÒ»¸ö±íÓëÁíÒ»¸ö±íµÄÄÚ²¿Á¬½Ó¡£ÕâЩ±íÔÚÁ½¸ö±íµÄ¹²ÓÐÁÐ(°´Ãû³Æ)ÉÏÁ¬½Ó¡£Èç¹ûÁ½¸ö±íûÓй«¹²ÁÐÃû£¬Ôò·µ»Ø´íÎó£¬Óï·¨ÈçÏ£º 



NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>)





NATURALLEFTOUTERJOIN()º¯ÊýÀàËÆÓÚSQLÖеÄLEFT JOIN¡£Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-041

VAR T1 =

FILTER ( ALL('DK'£Û²úÆ·£Ý, 'DK'£Û°ü×°·½Ê½£Ý,
'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý > 70 )

VAR T2 =

FILTER ( ALL('DK'£Û²úÆ·£Ý,'DK'£ÛÈë¿â£Ý), 'DK'£ÛÈë¿â£Ý < 90 )

RETURN

NATURALLEFTOUTERJOIN( T1, T2 )





·µ»ØµÄÖµÈçͼ3ª²38Ëùʾ¡£



ͼ3ª²38NATURALLEFTOUTERJOIN()º¯ÊýµÄÓ¦ÓÃ


3.7·Ö×é

DAXÖеÄSUMMARIZE()ÀàËÆÓÚSQLÖеÄGROUP BY·Ö×é²Ù×÷¡£ÔÚSQLÖУ¬WITH ROLLUPͨ³£ÓëGROUP BY×Ó¾äÒ»ÆðʹÓ㬸ù¾Ýά¶ÈÔÚ·Ö×éºó½øÐоۺϲÙ×÷¡£Í¬Àí£¬ÔÚDAXÖÐROLLUP()º¯ÊýÖ»ÄÜÔÚSUMMARIZE()±í´ïʽÖÐÒ»ÆðʹÓá£

3.7.1SUMMARIZE()

SUMMARIZE()º¯ÊýÓÃÓÚÉú³ÉÊý¾Ý»ã×Ü±í£¬Óï·¨ÈçÏ£º 



SUMMARIZE (

<table>, 

<groupBy_columnName>

£Û, <groupBy_columnName>£Ý¡­£Û, <name>, <expression>£Ý¡­

)





ÒÔDK±íÖеİü×°·½Ê½ÁÐΪ·Ö×éÒÀ¾Ý£¬¶ÔÈë¿âÁеÄÊý¾Ý½øÐзÖ×éͳ¼Æ£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-042

SUMMARIZE(

'DK',

'DK'£Û°ü×°·½Ê½£Ý,

"Èë¿âÁ¿",SUM('DK'£ÛÈë¿â£Ý)

)





·µ»ØµÄÖµÈçͼ3ª²39Ëùʾ¡£



ͼ3ª²39SUMMARIZE()º¯ÊýµÄÓ¦ÓÃ(1)


ÒÔDK±íÖеİü×°·½Ê½ÁÐΪ·Ö×éÒÀ¾Ý£¬¶ÔÈë¿âÁеÄÊý¾Ý½øÐзÖ×éͳ¼Æ£¬²¢É¸Ñ¡³öͳ¼ÆÖµ(Èë¿âÁ¿)´óÓÚ80µÄÊý¾Ý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-043

FILTER (

SUMMARIZE (

'DK',

'DK'£Û°ü×°·½Ê½£Ý,

"Èë¿âÁ¿", SUM ( 'DK'£ÛÈë¿â£Ý )

),

£ÛÈë¿âÁ¿£Ý > 80

)





·µ»ØµÄÖµÈçͼ3ª²40Ëùʾ¡£

ɸѡDK±íÖаü×°·½Ê½ÎªÏä×°ºÍÍ°×°µÄÊý¾Ý£¬È»ºóÒÔ°ü×°·½Ê½ÁÐΪ·Ö×éÒÀ¾Ý£¬¶ÔÈë¿âÁеÄÊý¾Ý½øÐзÖ×éͳ¼Æ£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-044

SUMMARIZE (

FILTER (

'DK',

'DK'£Û°ü×°·½Ê½£Ý IN  {"Ïä×°","Í°×°"}

),

'DK'£Û°ü×°·½Ê½£Ý,

"Èë¿âÁ¿", SUM ( 'DK'£ÛÈë¿â£Ý )

)





·µ»ØµÄÖµÈçͼ3ª²41Ëùʾ¡£



ͼ3ª²40SUMMARIZE()º¯ÊýµÄÓ¦ÓÃ(2)




ͼ3ª²41SUMMARIZE()º¯ÊýµÄÓ¦ÓÃ(3)



×¢Ò⣺ SUMMARIZE()ÊǸöÂýº¯Êý¡£Èç¹ûÔÚSUMMARIZE()º¯ÊýµÄʹÓùý³ÌÖÐÐèÒª²úÉúÅÉÉúÁУ¬Ôò½¨ÒéÔÚÍâΧǶÌ×ADDCOLUMNS()º¯Êý£¬²»ÒªÖ±½ÓʹÓÃSUMMARIZE()½øÐзÖ×é¾ÛºÏ£¬»òÕßÓÅÏȲÉÓÃSUMMARIZECOLUMNS()º¯Êý¡£³ý·ÇÐèÒªÔÚÒ»¸ö»ò¶à¸ö·Ö×éÁÐÉÏʹÓà ROLLUP()¼ÆËãÿ×éµÄ×ܼơ£Ö»ÓÐÅÉÉúÁÐʹÓÃÁËijЩ·Ç³£ÌØÊâµÄ±í´ïʽʱ²Å¿ÉÒÔ¿¼ÂÇÖ±½ÓʹÓÃSUMMARIZE()µÄ·Ö×é¾ÛºÏ¹¦ÄÜ¡£

3.7.2SUMMARIZECOLUMNS()

SUMMARIZECOLUMNS()º¯ÊýÓÃÓÚ·µ»ØÓÉÒ»×éÁÐÖ¸¶¨µÄÕªÒª±í£¬Óï·¨ÈçÏ£º 



SUMMARIZECOLUMNS(

<groupBy_columnName>

£Û, < groupBy_columnName >£Ý¡­, 

£Û<filterTable>£Ý¡­

£Û,<name>,<expression>£Ý¡­

)





1. Êý¾ÝÀ´×Ôͬһ¸ö±í

ÒÔ°ü×°·½Ê½ÁÐΪ·Ö×éÒÀ¾Ý£¬¶ÔÈë¿âÁеÄÊý¾Ý½øÐзÖ×éͳ¼Æ£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-045

SUMMARIZECOLUMNS (

'DK'£Û°ü×°·½Ê½£Ý,

"Èë¿âÁ¿", SUM ( 'DK'£ÛÈë¿â£Ý ),

"´ÎÊý", COUNT ( DK£ÛÈë¿â£Ý )

) 





·µ»ØµÄÖµÈçͼ3ª²42Ëùʾ¡£

ÒÔ°ü×°·½Ê½ÁÐΪ·Ö×éÒÀ¾Ý£¬¶ÔÈë¿âÁеÄÊý¾Ý½øÐÐÇóƽ¾ùÖµ£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-046

SUMMARIZECOLUMNS(

'DK'£Û°ü×°·½Ê½£Ý,

"Èë¿â¾ùÁ¿", AVERAGE('DK'£ÛÈë¿â£Ý)

)





·µ»ØµÄÖµÈçͼ3ª²43Ëùʾ¡£



ͼ3ª²42SUMMARIZECOLUMNS()º¯Êý
µÄÓ¦ÓÃ(1)




ͼ3ª²43SUMMARIZECOLUMNS()º¯Êý
µÄÓ¦ÓÃ(2)



ÒÔDK±íÖеİü×°·½Ê½Îª·Ö×éÒÀ¾Ý£¬É¸Ñ¡±íÖвúƷΪµ°¸âÖ½¡¢¾»»¯¼Á¡¢Æ»¹û´×µÄÐУ¬È»ºó¶ÔÈë¿âÁеÄÊý¾Ý½øÐзÖ×éͳ¼Æ£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-047

SUMMARIZECOLUMNS (

'DK'£Û°ü×°·½Ê½£Ý,

FILTER ( 'DK', 'DK'£Û²úÆ·£Ý IN {"µ°¸âÖ½", "¾»»¯¼Á", "Æ»¹û´×"} ),

"Èë¿âÁ¿", SUM ( 'DK'£ÛÈë¿â£Ý )

)







ͼ3ª²44SUMMARIZECOLUMNS()
º¯ÊýµÄÓ¦ÓÃ(3)


·µ»ØµÄÖµÈçͼ3ª²44Ëùʾ¡£

2. Êý¾ÝÀ´×ÔÊý¾ÝÄ£ÐÍ

ÒÔºÏͬ±íÖеİü×°·½Ê½ºÍÔ˵¥±íÖеIJúÆ·ÁÐΪ×éºÏÒÀ¾Ý£¬É¸Ñ¡Ô˵¥±íÖÐÊýÁ¿´óÓÚ3µÄÊý¾Ý£¬±í´ïʽÈçÏ£º 





EVALUATE¡¡¡¡//ch3-048

SUMMARIZECOLUMNS(

'ºÏͬ'£Û°ü×°·½Ê½£Ý,

'Ô˵¥'£Û²úÆ·£Ý,

FILTER( 'Ô˵¥', 'Ô˵¥'£ÛÊýÁ¿£Ý>3 )  )





µÈͬÓÚÒÔÉÏ´úÂ룬±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-049

SUMMARIZECOLUMNS(

'ºÏͬ'£Û°ü×°·½Ê½£Ý,

'Ô˵¥'£Û²úÆ·£Ý,

CALCULATETABLE( 'Ô˵¥', 'Ô˵¥'£ÛÊýÁ¿£Ý>3 )  ) 





·µ»ØµÄÖµÈçͼ3ª²45Ëùʾ¡£



ͼ3ª²45SUMMARIZECOLUMNS()º¯ÊýµÄÓ¦ÓÃ(4)


ÒÔºÏͬ±íÖеİü×°·½Ê½ºÍÔ˵¥±íÖеIJúƷΪ×éºÏ£¬É¸Ñ¡Ô˵¥±íÖÐÊýÁ¿´óÓÚ3µÄÐУ¬¶ÔɸѡºóµÄÊý¾Ý½øÐÐÇóºÍ¡¢Çóƽ¾ù£¬±í´ïʽÈçÏ£º 



EVALUATE  //ch3-050

SUMMARIZECOLUMNS(

'ºÏͬ'£Û°ü×°·½Ê½£Ý,

'Ô˵¥'£Û²úÆ·£Ý,

CALCULATETABLE( 'Ô˵¥', 'Ô˵¥'£ÛÊýÁ¿£Ý>3),

"ÊýÁ¿ºÍ", SUM('Ô˵¥'£ÛÊýÁ¿£Ý),

"¾ù³É±¾",  AVERAGE('Ô˵¥'£Û³É±¾£Ý) 

)





·µ»ØµÄÖµÈçͼ3ª²46Ëùʾ¡£



ͼ3ª²46SUMMARIZECOLUMNS()º¯ÊýµÄÓ¦ÓÃ(5)


3.7.3ROLLUP()

ROLLUP()ÓÃÓÚ±êʶSUMMARIZE()º¯ÊýÖÐÐèÒª¼ÆËãС¼ÆµÄÁС£´Ëº¯ÊýÖ»ÄÜÔÚSUMMARIZE()±í´ïʽÖÐʹÓã¬Óï·¨ÈçÏ£º 



ROLLUP ( 

<groupBy_columnName> £Û, 

<groupBy_columnName> £Û, ¡­ £Ý £Ý 

)





ROLLUP()º¯ÊýÖ»ÔÚSUMMARIZE()ÖÐʹÓ㬲¢ÇÒROLLUP()±í´ïʽÖÐÒýÓõÄÁв»ÄÜ×÷Ϊ SUMMARIZE()º¯ÊýµÄ·Ö×éÁУ¬±í´ïʽÈçÏ£º



EVALUATE¡¡//ch3-051

SUMMARIZE(

'Ô˵¥',

ROLLUP ( 'ºÏͬ'£Û°ü×°·½Ê½£Ý ),

"ÊýÁ¿ºÍ", SUM ( 'Ô˵¥'£ÛÊýÁ¿£Ý )

)

ORDER BY 'ºÏͬ'£Û°ü×°·½Ê½£Ý





ÔÚÊý¾Ý·ÖÎö¹ý³ÌÖУ¬Êý¾ÝµÄС¼Æ¡¢»ã×ܵÈÊôÓÚÉϾí(ROLLUP)²Ù×÷£¬ÊÇÊý¾ÝµÄ¿ÅÁ£¶ÈÓÉϸµ½´ÖµÄ¹ý³Ì£¬²Ù×÷ÖлáºöÂÔijЩά¶È£¬·µ»ØµÄÖµÈçͼ3ª²47Ëùʾ¡£



ͼ3ª²47ROLLUP()º¯ÊýµÄÓ¦ÓÃ(1)


Èçͼ3ª²47Ëùʾ£¬ÊýÁ¿ºÍÁÐÖУ¬¿ÕÐÐÁжÔÓ¦µÄÖµ154ÊǶÔÁÐÖÐÆäËûÖµµÄ×ܼơ£ÓëÉϾí²Ù×÷¶ÔÓ¦µÄÊÇÏÂ×ê(»ò³Æ¡°×êÈ¡¡±)²Ù×÷¡£Êý¾ÝµÄ¿ÅÁ£¶ÈÓÉ´Öµ½Ï¸µÄ¹ý³ÌÔòÊôÓÚÊý¾ÝµÄÏÂ×꣬²Ù×÷Öлáϸ»¯Ä³Ð©Î¬¶È¡£

·ÖÀà»ã×ÜÊý¾Ý²¢¶Ô¸÷°ü×°µÄÊýÁ¿½øÐÐС¼Æ£¬È»ºó¶ÔËùÓвúÆ·ÊýÁ¿½øÐÐͳ¼Æ£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-052

SUMMARIZE (

'Ô˵¥',

ROLLUP ( 'ºÏͬ'£Û°ü×°·½Ê½£Ý,  'ºÏͬ'£Û²úÆ·£Ý),

"ÊýÁ¿ºÍ", SUM ( 'Ô˵¥'£ÛÊýÁ¿£Ý )

)





·µ»ØµÄÖµÈçͼ3ª²48Ëùʾ¡£



ͼ3ª²48ROLLUP()º¯ÊýµÄÓ¦ÓÃ(2)


3.7.4ROLLUPISSUBTOTAL()

ROLLUPISSUBTOTAL()²»·µ»ØÖµ£¬ËüÖ»±ê¼ÇADDMISSINGITEMS()ÖÐÒª¼ÆËãС¼ÆµÄÁм¯£¬¸Ãº¯ÊýÖ»ÄÜÔÚADDMISSINGITEMS()ÖÐʹÓã¬Óï·¨ÈçÏ£º 



ROLLUPISSUBTOTAL ( 

£Û<grandTotalFilter>£Ý,

<groupBy_columnName>, 

<isSubtotal_columnName>

£Û, £Û<groupLevelFilter>£Ý 

£Û, <groupBy_columnName>,<isSubtotal_columnName>

£Û, £Û<groupLevelFilter>£Ý £Û, ¡­ £Ý £Ý £Ý £Ý 

)





ROLLUPISSUBTOTAL()º¯ÊýµÄ²ÎÊý˵Ã÷£¬¼û±í3ª²2¡£

±í3ª²2²ÎÊý˵Ã÷



²ÎÊý²Î Êý ˵ Ã÷

grandtotalFilter(¿ÉÑ¡) ÒªÓ¦ÓÃÓÚ×ܼƼ¶±ðµÄ¹ýÂËÆ÷
groupBy_columnNameÓÃÓÚ¸ù¾ÝÔÚÆäÖÐÕÒµ½µÄÖµ´´½¨»ã×Ü×éµÄÏÖÓÐÁеÄÃû³Æ£¬²»ÄÜÊDZí´ïʽ
isSubtotal_columnNameISSUBTOTALÁеÄÃû³Æ£¬ÁеÄֵʹÓÃISSUBTOTALº¯Êý¼ÆËã
groupLevelFilter(¿ÉÑ¡) ÒªÓ¦ÓÃÓÚµ±Ç°¼¶±ðµÄ¹ýÂËÆ÷


Ïà¹ØÓ¦ÓþÙÀý£¬²Î¼ûADDKISSINGITEMS()º¯ÊýÕ½ڡ£

3.7.5ROLLUPADDISSUBTOTAL()

ROLLUPADDISSUBTOTAL()º¯ÊýÓÃÓÚ±êʶSUMMARIZECOLUMNS()º¯ÊýÖÐÐèÒª¼ÆËãС¼ÆµÄÁУ¬·µ»ØµÄֵΪTRUE»òFALSE£¬Óï·¨ÈçÏ£º 



ROLLUPADDISSUBTOTAL ( 

£Û<grandtotalFilter>£Ý, 

<groupBy_columnName>, 

<name>

£Û, £Û<groupLevelFilter>£Ý

£Û, <groupBy_columnName>, <name>

£Û, £Û<groupLevelFilter>£Ý £Û, ¡­ £Ý £Ý £Ý £Ý

)





ROLLUPADDISSUBTOTAL()º¯ÊýµÄ²ÎÊý˵Ã÷£¬¼û±í3ª²3¡£

±í3ª²3²ÎÊý˵Ã÷



²ÎÊý²Î Êý ˵ Ã÷

grandtotalFilter(¿ÉÑ¡) ÒªÓ¦ÓÃÓÚ×ܼƼ¶±ðµÄɸѡÆ÷
groupBy_columnNameÓÃÓÚ¸ù¾ÝÁÐÖеÄÖµ´´½¨ÕªÒª×éµÄÏÖÓÐÁеÄÃû³Æ£¬²»ÄÜÊDZí´ïʽ
nameISSUBTOTALÁеÄÃû³Æ£¬Ê¹ÓÃISSUBTOTALº¯Êý¼ÆËãÁеÄÖµ
groupLevelFilter(¿ÉÑ¡) ÒªÓ¦ÓÃÓÚµ±Ç°¼¶±ðµÄɸѡÆ÷



SUMMARIZECOLUMNS()ÖÐǶÌ×ROLLUPADDISSUBTOTAL()£¬Æ书ÄÜÀàËÆÓÚSUMMARIZE()ÖÐǶÌ×ROLLUP()£¬ÓÃÓÚС¼ÆÐеıêʶ¡£

¶ÔÔ˵¥±íÖеIJúÆ·¡¢ÊýÁ¿ÁкÍÈÕÆÚ±íÖеÄÄê½øÐÐ×éºÏ£¬È»ºó±êʶÐèҪС¼ÆµÄÐУ¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-053

TOPN (

5,

FILTER (

SUMMARIZECOLUMNS (

'Ô˵¥'£Û²úÆ·£Ý,

'Ô˵¥'£ÛÊýÁ¿£Ý,

ROLLUPADDISSUBTOTAL ('ÈÕÆÚ'£ÛÄê£Ý,"Äê·Ý")

),

£ÛÄê·Ý£Ý = TRUE

)

) 







ͼ3ª²49ROLLUPISSUBTOTAL()
º¯ÊýµÄÓ¦ÓÃ


·µ»ØµÄÖµ¹²96ÐУ¬(½ØÈ¡Ç°5ÐÐ)ÐèҪС¼ÆµÄÐÐÒѱ»±êʶΪTRUE£¬²»ÐèҪС¼ÆµÄÐб»±êʶΪFALSE£¬Èçͼ3ª²49Ëùʾ¡£

ͼ3ª²48ÖУ¬ÄêËùÔÚÁеĿÕÖµ²¿·Ö£¬ÆäÄê·Ý¶ÔÓ¦µÄ±êʶ¾ùΪTRUE£¬¸ÃÐÐÊÇС¼ÆÐС£



3.7.6ADDMISSINGITEMS()

ADDMISSINGITEMS()ÓÃÓÚ½«¾ßÓпÕÖµµÄÐÐÌí¼Óµ½ SUMMARIZECOLUMNS()·µ»ØµÄ±íÖУ¬Óï·¨ÈçÏ£º 



ADDMISSINGITEMS ( 

£Û<showAll_columnName>

£Û, <showAll_columnName>

£Û, ¡­ £Ý £Ý £Ý, 

<table>

£Û,<groupBy_columnName>

£Û, £Û<filterTable>£Ý 

£Û, <groupBy_columnName>£Û, £Û<filterTable>£Ý £Û, ¡­ £Ý £Ý £Ý £Ý £Ý £Ý

)





ADDMISSINGITEMS()º¯ÊýµÄ²ÎÊý˵Ã÷¼û±í3ª²4¡£

±í3ª²4ADDMISSINGITEMS()º¯ÊýµÄ²ÎÊý˵Ã÷



²ÎÊý²Î Êý ˵ Ã÷

showAll_columnName(¿ÉÑ¡) ҪΪÆä·µ»ØδʹÓöÈÁ¿ÖµÊý¾ÝµÄÏîµÄÁУ¬Èç¹ûδָ¶¨£¬Ôò·µ»ØËùÓÐÁÐ
tableSUMMARIZECOLUMNS±í
groupBy_columnName(¿ÉÑ¡) ÓÃÓÚÔÚÌṩµÄtable²ÎÊýÖзÖ×éµÄÁÐ
filterTable(¿ÉÑ¡) ¶¨Òå·µ»ØÄÄЩÐеıí±í´ïʽ


¸Ãº¯ÊýÓÃÓÚÌí¼ÓÓÉÓÚÐÂÁеıí´ïʽ·µ»Ø¿ÕÖµ¶ø±»SUMMARIZECOLUMNS()Òþ²ØµÄÐУ¬±í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-054

ADDMISSINGITEMS (

'Ô˵¥'£Û°ü×°·½Ê½£Ý ,

SUMMARIZECOLUMNS(

'Ô˵¥'£Û°ü×°·½Ê½£Ý ,

"Çóƽ¾ùÖµ", AVERAGE('Ô˵¥'£Û³¬¶î·ÑÓÃ£Ý )

),

'Ô˵¥'£Û°ü×°·½Ê½£Ý

)





·µ»ØµÄÖµÈçͼ3ª²50Ëùʾ¡£

¼ÌÐø¾ÙÀýADDMISSINGITEMS()µÄÓ¦Ó㬱í´ïʽÈçÏ£º 



EVALUATE¡¡//ch3-055

ADDMISSINGITEMS (

'Ô˵¥'£Û°ü×°·½Ê½£Ý,

SUMMARIZECOLUMNS (

'Ô˵¥'£Û°ü×°·½Ê½£Ý,

"ÊýÁ¿ºÍ", CALCULATE ( 

SUM ( 'Ô˵¥'£ÛÊýÁ¿£Ý ),

FILTER ( ALL ( 'Ô˵¥'£ÛÊýÁ¿£Ý ), 'Ô˵¥'£ÛÊýÁ¿£Ý <= 5 ) 

)

),

'Ô˵¥'£Û°ü×°·½Ê½£Ý

) 





·µ»ØµÄÖµÈçͼ3ª²51Ëùʾ¡£



ͼ3ª²50Ìí¼Ó¾ßÓпÕÖµµÄÐÐ(1)




ͼ3ª²51Ìí¼Ó¾ßÓпÕÖµµÄÐÐ(2)



3.8´´½¨±í
3.8.1±í¹¹ÔìÆ÷{()}

ÔÚDAXÖÐ±í¹¹ÔìÆ÷ÓÉ{}ºÍ()×é³É¡£{}´ú±íµÄÊÇÒ»ÁеÄÊý¾Ý£¬()´ú±íµÄÊÇÒ»ÐеÄÊý¾Ý¡£Èç¹û±íµÄÊý¾ÝÖ»ÓÐÒ»ÁУ¬ÔòСÀ¨ºÅ¿ÉÒÔÊ¡ÂÔ£¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-056

{ "É¢×°", "Ïä×°", "Í°×°" } //±êÌâΪ"Value"µÄÒ»ÁÐÊý¾Ý£¬¹²3ÐÐ





ÁбíÄÚÿ¸öÀ¨ºÅ´ú±íµÄÊÇÒ»ÐУ¬µ±Ö»ÓÐÒ»ÁÐʱ£¬À¨ºÅ¿ÉÒÔÊ¡ÂÔ£¬±í´ïʽÈçÏ£º



EVALUATE¡¡¡¡//ch3-057

{ ("É¢×°"), ("Ïä×°"), ("Í°×°") } //±êÌâΪ"Value"µÄÒ»ÁÐÊý¾Ý





·µ»ØµÄÖµÈçͼ3ª²52Ëùʾ¡£

ÔÚ±í¹¹ÔìÆ÷ÄÚ£¬{}´ú±íµÄÊÇÁÐÖµ£¬()´ú±íµÄÊÇÐÐÖµ£¬¡°,¡±´ú±íµÄÊÇÊý¾Ý·Ö¸ô·û¡£Èç¹ûÐèÒª´´½¨µÄÊÇÒ»¸ö¶àÁÐµÄ±í£¬ÔòÿÐеÄÊý¾ÝÐèÓÃСÀ¨ºÅ·Ö¸ô£¬Êý¾ÝÓëÊý¾Ý¼äÓöººÅ·Ö¸ô¡£ÒÔ´´½¨Ò»¸öÈýÐÐËÄÁеıíΪÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-058

{

("Ïä×°","µ°¸âÖ½",2,15),  //()ÄÚ","ΪÁзָô·û

("É¢×°","¸Ö»¯Ä¤",3,5),

("Í°×°","ÓÍÆá",5,9)

}





·µ»ØµÄÖµÈçͼ3ª²53Ëùʾ¡£



ͼ3ª²52´´½¨µ¥Áеıí




ͼ3ª²53´´½¨3ÐÐ4Áеıí



3.8.2DATATABLE()

DATATABLE£¨£©º¯ÊýÓÃÓÚ¿ìËÙ´´½¨½á¹¹¼òµ¥µÄ±í£¬Ö§³ÖµÄÊý¾ÝÀàÐÍÓÐÕûÊý(INTEGER)¡¢Ë«¾«¶È(DOUBLE)¡¢×Ö·û´®(STRING)¡¢²¼¶ûÖµ(BOOLEAN)¡¢»õ±Ò(CURRENCY)¡¢ÈÕÆÚʱ¼ä(DATETIME)£¬Óï·¨ÈçÏ£º 



DATATABLE (

ColumnName1, DataType1, 

ColumnName2, DataType2..., 

{

{Value1, Value2...}, 

{ValueN,ValueN+1...}...

}

)





DATATABLE()±íµÄÄÚÈݱØÐëÊdz£Á¿£¬²»Ö§³ÖÈκÎDAX ±í´ïʽ£¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-059

DATATABLE (

"²úÆ·", string, 

"°ü×°·½Ê½", string,

"Èë¿â",integer, 

{

{"¸Ö»¯Ä¤", "É¢×°", 76}, 

{"µ°¸âÖ½", "Ïä×°", 32}

}

)







ͼ3ª²54´´½¨¼òµ¥µÄ±í


ÔÚDAXÖÐÖ÷ÒªÓÐ6ÖÖÊý¾ÝÀàÐÍ£º BOOLEAN¡¢STRING¡¢DOUBLE¡¢INTEGER¡¢CURRENCY¡¢DATETIME¡£DAXÖУ¬¶Ôº¯Êý¡¢±äÁ¿Ãû¡¢ÎļþÃûµÄ×Öĸ´óСд²»Ãô¸Ð¡£ÒÔÉϱí´ïʽ·µ»ØµÄÖµÈçͼ3ª²54Ëùʾ¡£


DATATABLE()º¯Êý¶à¼ûÓÚ²ÎÊý±íµÄ´´½¨¹ý³ÌÖУ¬Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-060

DATATABLE (

"Çø¼ä", STRING,

"Æðʼֵ", CURRENCY,

"½áÊøÖµ", CURRENCY,

{

{ "²î", 0, 59 },

{ "ÖÐ", 60, 79 },

{ "Á¼", 80, 89 },

{ "ÓÅ", 90, 100 }

}

)





·µ»ØµÄÖµÈçͼ3ª²55Ëùʾ¡£

3.8.3ROW()

ROW()º¯ÊýÓÃÓÚ·µ»ØÒ»¸ö¾ßÓе¥ÐÐµÄ±í£¬ÆäÖаüº¬Õë¶ÔÿÁмÆËã±í´ïʽµÃ³öµÄÖµ£¬Óï·¨ÈçÏ£º 



ROW(<name>, <expression>£Û£Û,<name>, <expression>£Ý¡­£Ý)





µÚ1¸ö²ÎÊýnameΪָ¶¨µÄÁÐÃû£¬±ØÐëÓÃË«ÒýºÅÒýÆðÀ´¡£µÚ2¸ö²ÎÊýΪDAX±í´ïʽ¡£´´½¨DAX²éѯ£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-061

ROW("ÊýÁ¿ºÍ",SUM('Ô˵¥'£ÛÊýÁ¿£Ý),"³É±¾ºÍ",SUM('Ô˵¥'£Û³É±¾£Ý))





·µ»ØµÄÖµÈçͼ3ª²56Ëùʾ¡£



ͼ3ª²55´´½¨µÄ²ÎÊý±í




ͼ3ª²56ROWº¯ÊýËù·µ»ØµÄ±í




3.9×ÛºÏÓ¦ÓÃ
3.9.1TOPN()

TOPN()º¯ÊýÓÃÓÚ»ñÈ¡Ö¸¶¨±íÖеÄÇ°NÐÐÊý¾Ý£¬Óï·¨ÈçÏ£º 



TOPN(

<n_value>,

<table>,

<orderBy_expression>, 

£Û<order>£Û,<orderBy_expression>, £Û<order>£Ý£Ý¡­£Ý

)





µÚ4¸ö²ÎÊýorderΪ¿ÉÑ¡²ÎÊý£¬ÓÃÓÚÖ¸¶¨µÚ3¸ö²ÎÊý(orderBy_expression)ÖµµÄÅÅÐò·½Ê½(ĬÈÏֵΪ0£¬½µÐò£» ÉýÐòΪ1)¡£Ó¦ÓþÙÀý£¬Ñ¡ÔñÔ˵¥±íÖеIJúÆ·ºÍÊýÁ¿ÁУ¬²¢¶Ô¸÷²úÆ·µÄÊýÁ¿ÇóºÍ£¬ÌôÑ¡±íÖÐÊýÁ¿ºÍÅÅÃûÇ°3µÄÊý¾Ý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-062

TOPN (

3,








SUMMARIZECOLUMNS (

'Ô˵¥'£Û²úÆ·£Ý,

"ÊýÁ¿ºÍ",SUM('Ô˵¥'£ÛÊýÁ¿£Ý)

),

£ÛÊýÁ¿ºÍ£Ý

)







ͼ3ª²57´´½¨µÄ±í


·µ»ØµÄÖµÈçͼ3ª²57Ëùʾ¡£

3.9.2À©Õ¹±íÀíÂÛ

±íµÄÀ©Õ¹ÊÇ´Ó¹ØϵµÄ¶à¶ËÏòÒ»¶Ë½øÐеġ£À©Õ¹±í°üº¬¹Øϵ¶à¶ËÖеÄËùÓÐÁÐ(Ô­ÉúÁÐ)£¬ÒÔ¼°´¦ÓÚ¹Øϵһ¶ËÖеÄËùÓеıíºÍÁÐ(Ïà¹ØÁÐ)¡£ÒÔSUMMARIZE()Ó¦ÓÃΪÀý£¬À©Õ¹À´×ÔÊý¾Ý¶à¶ËµÄ±íºÍÀ´×ÔÊý¾ÝÒ»¶ËµÄÁУ¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-063

SUMMARIZE('Ô˵¥','ºÏͬ'£Û²úÆ·£Ý)





À©Õ¹±íÖ»»áÓɶà¶ËÏòÒ»¶ËÀ©Õ¹£¬À©Õ¹ºóµÄ±í°üº¬Ò»¶ËµÄËùÓÐÁУ¬Êý¾ÝÄ£Ðͼ°·µ»ØµÄÖµÈçͼ3ª²58Ëùʾ¡£



ͼ3ª²58´´½¨·Ö×éÁÐ


ÔÚÊý¾ÝÄ£ÐÍÖУ¬±íµÄÀ©Õ¹ÊDz»ÄÜ´ÓÒ»¶ËÏò¶à¶Ë½øÐеġ£¶ÔÒÔÉϱí´ïʽ½øÐÐÐ޸ģ¬Ð޸ĺóµÄ±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-064

SUMMARIZE('ºÏͬ','Ô˵¥'£Û²úÆ·£Ý)





ÒòÔÚºÏͬ±íµÄÀ©Õ¹±íÖÐÕÒ²»µ½²úÆ·ÁУ¬ËùÒÔϵͳ±¨´íÌáʾ£¬Èçͼ3ª²59Ëùʾ¡£



ͼ3ª²59·µ»ØµÄ´íÎóÌáʾ


À©Õ¹±í×ÛºÏÓ¦Óã¬ÔÚÊý¾ÝÄ£ÐͶà¶ËµÄÔ˵¥±íÖлñÈ¡²úÆ·ÁУ¬¶ÔÒ»¶ËµÄ¶©µ¥±íÖеIJúÆ·ÁнøÐмÆÊý£¬¶ÔÒ»¶ËµÄ×°»õ±íÖеİü×°·½Ê½ÁнøÐмÆÊýºÍͳ¼Æ£¬¶ÔÒ»¶ËµÄ×°»õ±íÖеÄÖÊÁ¿ÁнøÐÐÇóºÍͳ¼Æ£¬¶ÔÒ»¶ËµÄÊÕ»õ±íÖеÄÊÕ»õÈ˽øÐÐÈ¥ÖØͳ¼Æ£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-065

SUMMARIZE (

'Ô˵¥',

'Ô˵¥'£Û²úÆ·£Ý,

"²úÆ·Êý", COUNT ( '¶©µ¥'£Û²úÆ·£Ý ),

"°ü×°Êý", COUNT ( '×°»õ'£Û°ü×°·½Ê½£Ý ),

"×°»õÊý", SUM ( '×°»õ'£ÛÖÊÁ¿£Ý ),

"·ÇÖظ´ÊÕ»õÈË", DISTINCTCOUNT ( 'ÊÕ»õ'£ÛÊÕ»õÈË£Ý )

)





·µ»ØµÄÖµÈçͼ3ª²60Ëùʾ¡£



ͼ3ª²60À©Õ¹±í·µ»ØµÄÖµ


×¢Ò⣺ ÔÚ¶à¶Ô¶à¹ØϵÖв»´æÔÚ±íµÄÀ©Õ¹¡£

3.9.3´´½¨¶ÈÁ¿±í

ÔÚÈÕ³£µÄÊý¾Ý´¦ÀíÓë·ÖÎö¹ý³ÌÖУ¬¾­³£»á´´½¨´óÁ¿µÄ¶ÈÁ¿Öµ£¬´Ëʱ¶Ô¶ÈÁ¿ÖµµÄ¹ÜÀíÒ²½«³ÉΪ¹¤×÷ЧÂÊÌáÉýµÄÒ»²¿·Ö¡£µ±Ä£ÐÍÖÐн¨µÄ¶ÈÁ¿ÖµÌرð¶àµÄÇé¿öÏ£¬ÐÂÔöÒ»¸ö¿Õ±íÓÃÓÚרÃÅÊÕÄɶÈÁ¿ÖµÊǷdz£ÓбØÒªµÄ¡£Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE//ch3-066

ROW("¶ÈÁ¿Öµ",BLANK())





ÔÚÉú³ÉµÄ²éѯ±íÖУ¬Ñ¡ÔñExcel¹¦ÄÜÇøµÄ¡°±íÉè¼Æ¡±¡ú¡°±íÃû³Æ¡±£¬½«±íÃû³ÆÃüÃûΪ¶ÈÁ¿£¬Èçͼ3ª²61Ëùʾ¡£



ͼ3ª²61Ð޸ıíÃû³Æ


Ñ¡ÔñExcel¹¦ÄÜÇøµÄPower Pivot¡ú¡°Ìí¼Óµ½Êý¾ÝÄ£ÐÍ¡±£¬Èçͼ3ª²62Ëùʾ¡£



ͼ3ª²62½«¿Õ±íÌí¼Óµ½Êý¾ÝÄ£ÐÍ


3.9.4´´½¨Î¬¶È±í

´´½¨Î¬¶È±í£¬ÓÃÓÚ»ñȡװ»õ±íºÍÔ˵¥±íÖеIJúÆ·×ֶεÄÈ¥ÖØÊý¾Ý£¬ÐγÉÒ»·ÝÍêÕûµÄ²úÆ·ÁÐ±í£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-067

SUMMARIZE(

UNION(VALUES('×°»õ'£Û²úÆ·£Ý),VALUES('Ô˵¥'£Û²úÆ·£Ý) ),

£Û²úÆ·£Ý

)





SUMMARIZE()º¯Êý¾ß±¸È¥Öع¦ÄÜ£¬ÊµÏÖ¹ý³Ì¼°·µ»ØµÄÖµÈçͼ3ª²63Ëùʾ¡£



ͼ3ª²63´´½¨²úÆ·Áбí


´´½¨Î¬¶È±í£¬ÓÃÓںϲ¢¶©µ¥±íºÍÔ˵¥±íÖвúÆ·ºÍ°ü×°·½Ê½¶þÁеÄÈ¥ÖغóÊý¾Ý£¬É¸Ñ¡³ö°ü×°·½Ê½º¬¡°×°¡±×ÖµÄÊý¾Ý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-068

FILTER (

DISTINCT (

UNION ( 

ALL ( '¶©µ¥'£Û²úÆ·£Ý, '¶©µ¥'£Û°ü×°·½Ê½£Ý ), 

ALL ( 'Ô˵¥'£Û²úÆ·£Ý, 'Ô˵¥'£Û°ü×°·½Ê½£Ý ) 

)

),

IFERROR ( FIND ( "×°", £Û°ü×°·½Ê½£Ý ), 0 ) > 1

) 





·µ»ØµÄÖµÈçͼ3ª²64Ëùʾ¡£



ͼ3ª²64Ó¦ÓÃ(1)


¶Ô²úÆ·±íÖеÄľ²ÄºÍ¸Ö²Ä²úÆ·ÓëºÏͬ±íÖеÄÀ¦¡¢Ôú¡¢Ä¤°ü×°·½Ê½Éú³ÉµÑ¿¨¶ù»ý±í£¬È»ºóͳ¼ÆÔ˵¥±íÖеIJúÆ·ÐÐÊý¡£Ó¦ÓþÙÀý£¬±í´ïʽÈçÏ£º 



EVALUATE¡¡¡¡//ch3-069

GENERATE (

SUMMARIZE (

FILTER('Ô˵¥','Ô˵¥'£Û²úÆ·£Ý IN {"ľ²Ä","¸Ö²Ä"}),

'Ô˵¥'£Û²úÆ·£Ý

),

SUMMARIZE(

FILTER('ºÏͬ','ºÏͬ'£Û°ü×°·½Ê½£Ý IN {"À¦","Ôú","Ĥ"}),

'ºÏͬ'£Û°ü×°·½Ê½£Ý,

"ÊýÁ¿",

COUNTROWS (

RELATEDTABLE ( 'Ô˵¥' )

)

)

)





·µ»ØµÄÖµÈçͼ3ª²65Ëùʾ¡£



ͼ3ª²65Ó¦ÓÃ(2)


½«ÒÔÉϱí´ïʽÖеÄGENERATE()º¯ÊýÌ滻ΪGENERATEALL()º¯Êý£¬·µ»ØµÄÖµÈçͼ3ª²66Ëùʾ¡£



ͼ3ª²66Ó¦ÓÃ(3)


3.10±¾Õ»عË

Power PivotÊý¾Ý¿âÒ²³ÆΪExcelÊý¾ÝÄ£ÐÍ£¬DAXÊÇExcelÊý¾ÝÄ£Ð͵ĺ¯Êý¹«Ê½ÓïÑÔ¡£ÔÚʹÓùý³ÌÖУ¬´¦ÀíÓë·ÖÎöÉÏ°ÙÍò¡¢Ç§ÍòÐ춱ðµÄÊý¾ÝÊdz£ÓеÄÊ¡£ÔÚδÄÜÁìÎòDAXº¯ÊýµÄÓ÷¨¼°¸÷º¯Êý¼äµÄϸ΢²îÒìʱ£¬Èç¹ûóȻʹÓø´ÔÓµÄÂß¼­È¥´¦Àí´óÈÝÁ¿µÄÊý¾Ý£¬Ôòµ½Ê±²»µÃ²»Ãæ¶ÔÊý¾ÝÔ¤ÆÚÖµ¡¢×¼È·ÐÔ¼°ÔËÐÐЧÂʵȵĶàÖØ¿¼Ñé¡£

±¾Õ¶ÔÊý¾ÝÄ£ÐÍÖг£¼û¡°É¸Ñ¡¡¢¼¯ºÏÓëʼþ¡¢µÑ¿¨¶ù»ý¡¢Á¬½Ó¡¢Í¶Ó°¡±µÈ²Ù×÷·Ö±ð½øÐÐÁ˽éÉÜ¡£±¾ÕÂÖصã¶ÔALL()ÓëVALUES()¡¢FILTER()ÓëCALCULATETABLE()¡¢SUMMARIZE()ÓëSUMMARIZECOLUMNS()¡¢GENERATE()ÓëGENERATEALL()µÄÓ÷¨²îÒì½øÐÐÁ˱ȽÏ˵Ã÷¡£ÌرðÊÇALL()ÓëVALUES()µÄ˵Ã÷¡£ALL()ÓëVALUES()ÔÚFILTEER()ÖÐʹÓÃʱ£¬ËüÃÇÊÇ±íº¯Êý;ÔÚCALCULATE()»òCALCULATETABLE()ÖÐʹÓÃʱ£¬ËüÃÇÊǵ÷½ÚÆ÷¡£ÕâЩ֪ʶ¶¼ÊÇΪºóÐøÄÜд³ö¸´ÔÓ¡¢×¼È·µÄDAX±í´ïʽ¶ø×¼±¸µÄ¡£

±¾ÕÂµÄ±íº¯ÊýÊÊÓÃÓÚDAX±í´ïʽÖвÎÊýΪ±íµÄÓ¦Óó¡¾°¡£±¾ÕÂËùÉæ¼°µÄ֪ʶµã½Ï¶àÇÒÓÐÒ»¶¨µÄÄѶȡ£ÎªÁË·½±ã¶ÁÕßÀí½â£¬±¾Õ°¸ÀýÒÑ×öµ½Á˾¡¿ÉÄܵؼò»¯ÓëÁ¬¹á¡£ÔÚDAXѧϰÓëʹÓõĹý³ÌÖУ¬Ö»ÓÐÔÚÊìϤ»ù´¡Óï·¨Ö®ºó£¬²ÅÄÜÓиü¶àµÄ¾«Á¦¾Û½¹ÓÚÔËËãÂß¼­ÓëÒµÎñ¹æÔòµÄ¹¹½¨ÉÏ£¬´Ó¶ø·¢»ÓDAXµÄ¾Þ´óÍþÁ¦¡£

ÒÔSUMMARIZE()º¯Êý·µ»ØµÄ±íΪÀý¡£ÏÖ´òË㽫SUMMARIZE()·µ»ØµÄ±í·ÅÖÃÓÚSUMX()º¯ÊýµÄµÚ1¸ö²ÎÊý(Ϊ±íº¯Êý)ÖУ¬ÓÃÓÚ¼ÆËã¸÷°ü×°·½Ê½µÄÈë¿âÁ¿(ÇóºÍ)¡£Ïà¹Ø¶ÈÁ¿ÖµµÄ±í´ïʽÈçÏ£º 



M.Èë¿âÁ¿:=

SUMX (

SUMMARIZE ( 'DK', 'DK'£Û°ü×°·½Ê½£Ý, 'DK'£ÛÈë¿â£Ý ),

'DK'£ÛÈë¿â£Ý

)-- ch3-070





ɸѡDK±íÖÐÈë¿âÁ¿´óÓÚ60µÄÊý¾Ý£¬È»ºó¶Ô¸÷°ü×°·½Ê½µÄÈë¿âÁ¿ÇóºÍ£¬Ïà¹Ø¶ÈÁ¿Öµ±í´ïʽÈçÏ£º 



M.ɸѡÈë¿âÁ¿:=SUMX (

SUMMARIZE ( 

FILTER ( 'DK', 'DK'£ÛÈë¿â£Ý > 60 ), 'DK'£Û°ü×°·½Ê½£Ý, 

'DK'£ÛÈë¿â£Ý 

),

'DK'£ÛÈë¿â£Ý

)-- ch3-071





´´½¨Í¸ÊÓ±í£¬½«°ü×°·½Ê½ÍÏÈëÐбêÇ©£¬¹´Ñ¡M.Èë¿âÁ¿ºÍM.ɸѡÈë¿âÁ¿ÕâÁ½¸ö¶ÈÁ¿Öµ£¬Í¸ÊÓ±íµÄ½á¹ûÈçͼ3ª²67Ëùʾ¡£



ͼ3ª²67͸ÊÓ±íÖеĶÈÁ¿Öµ±È½Ï


ÔÚDAXÖУ¬Óв»ÉÙº¯ÊýµÄµ¥´ÊËäÈ»½Ï³¤µ«ÆäʵÓйæÂÉ¿ÉÑ­£¬ºÜ¶àµÄº¯ÊýÆäʵÊǶà¸öµ¥´ÊµÄÓлúÆ´½Ó¡£ÀýÈçNATURALINNERJOIN()£¬ËüÊÇÓÉNatural+Inner+Join×é³É£» SUMMARIZECOLUMNS()£¬ËüÊÇÓÉSummarize+Columns×é³É£» ADDMISSINGITEMS()ÔòÊÇÓÉAdd+Missing+Items×é³É¡£ÆäËû³¤º¯Êý¾ù¿ÉÒÔ´ËÀàÍÆ£¬²»ÔÙÒ»Ò»¾ÙÀý¡£

ÔÚ±¾ÕÂʾÀýµÄ»ù´¡ÉÏ£¬¶ÁÕßÈÔÐèÀ©Õ¹ÁìÎò¼°ÊìϤ¸÷º¯Êý¼°Ó÷¨£¬ÒÔ±ãÔÚºóÐøÓ¦Óùý³ÌÖÐÄÜÇáËɼÝÔ¦¸÷ÀิÔÓÒµÎñÐèÇó¡£