SO HERE, YOU DON'T ATTEND THE BI POWER HOUR AND YOU WANT TO LOOK -- YOU WANT TO ATTEND THE DAX EVALUATION CONTEXT SESSION, SO WELCOME. MY NAME IS MARCO RUSSO. I'M HERE TODAY TO DESCRIBE HOW THE EVALUATION CONTEXT WORKS IN DAX IN ONE HOUR. SO BELIEVE ME, IS HARD TO DESCRIBE IN DETAIL EVERYTHING IN A WAY THAT SHOULD BE, YOU KNOW, RELATIVELY EASY TO UNDERSTAND, SO FOR SOME OF YOU IT WILL BE A RECAP. FOR SOME OF YOU IT WILL BE SOMETHING NEW, AND IN THAT CASE, I DON'T EXPECT YOU TO CAPTURE ALL THE DETAILS THAT YOU WILL SEE, BUT I WILL TRY TO BE AS CLEAR AS POSSIBLE, AND IF YOU HAVE SOME QUESTION IN THE MEANTIME, DON'T BE AFRAID TO RAISE YOUR HAND. THIS COULD BE COMPLEX TOPIC, SO I ACCEPT THE QUESTIONS DURING THE SESSION. DON'T BE AFRAID TO ASK. SO WHAT IS THE EVALUATION CONTEXT? WHEN YOU WRITE A DAX EXPRESSION, THE DAX EXPRESSION IS EXECUTED AND PROVIDES A RESULT, AND EVEN THOUGH YOU WRITE THE VERY SAME DAX EXPRESSION IN EVERY CELL OF YOUR REPORT YOU SEE A DIFFERENT NUMBER. WHY? BECAUSE SOMETHING HAPPENS BEHIND THE SCENES, AND EVEN, EWROTE A SIMPLE DAX EXPRESSION, A DAX QUERY, IN REALITY THERE ARE OTHER ELEMENTS THAT AFFECT THE RESULT OF THE CALCULATION. THESE ELEMENTS ARE WHAT WE CALL THE EVALUATION CONTEXT. AN EVALUATION CONTEXT IS MADE BY TWO MAIN CONCEPTS WHICH ARE THE FILTER CONTEXT AND THE ROW CONTEXT, BUT WE HAVE ONE IMPORTANT ELEMENT WHICH IS THE CONTEXT POSITION THAT CAN PROVIDE SIDE EFFECTS IN WHAT WE OBSERVE AS A RESULT OF OUR EVALUATION. SO WE NEED TO INTRODUCE SOME THEORY IN WHAT WE SEE BECAUSE, OF COURSE, THE EVALUATION CONTEXT REQUIRES SOME BACKGROUND, THEET RHETTICAL BACKGROUND. LET'S SAY I HAVE A MEASURE. A MEASURE IS A SIMPLE SUM WHERE I SUM ONE COLUMN OF MY MODEL. THIS IS THE SIMPLEST CONTEXT YOU CAN HAVE IN DAX, SUM MODEL. WHEN I EXECUTE THIS MEASURE IN POWER BI, IN EXCEL, IN ANY REPORTING TOOL, I GET AS A RESULT THE SUM OF ALL THE ROWS OF THE TABLE THAT HAS THAT COLUMN. THE TOTAL SAYS HERE IS THE SUM OF ALL THE ROWS OF THE TABLE. BUT AS SOON AS YOU INTRODUCE SOME OTHER ELEMENT IN THE REPORT, FOR EXAMPLE, YOU CREATE A METRICS OR YOU CREATE A PIVOT TABLE AND YOU INCLUDE -- IN THE ROWS, THEN EVERY ROW HAS A DIFFERENT VALUE. AND YOU SAY, MARCO, WE WANT TO SEE THE SUM OF THE SALES FOR EACH COLOR. OKAY, THIS IS THE NATURAL DESCRIPTION OF WHAT WE WANT TO DO, BUT WHY? THE MEASURE THAT WE'VE WROTE, SUM OF -- IS ACTUALLY ONLY CONSIDERING A FEW ROWS OF THE SALES TABLE WHILE THE FILTER THAT WE ARE POTENTIALLY APPLYING IS ON ANOTHER COLUMN OF ANOTHER TABLE. SO WHAT IS EXACTLY THE TECHNIQUE THAT PRODUCES A DIFFERENT RESULT? WELL, THIS TECHNIQUE IS THE FILTER CONTEXT. THE FILTER CONTEXT IS SOMETHING THAT IMPLICITLY FILTERS THE EXPRESSIONS THAT WE WRITE BECAUSE WE APPLY THE SUM FILTER. NOW HOW CAN WE REPRESENT THESE FILTERS? BECAUSE THE FIRST IDEA IS THAT, OKAY, IF I LOOK AT USER INTERFACE FOR REPORT, I CAN SEE THAT WE HAVE ELEMENTS. AND IN POWER BI THIS COULD BE COMPLEX, BECAUSE IN POWER BI YOU HAVE EVERY VISUALIZATION CAN POTENTIALLY AFFECT THE RESULT OF OTHER VISUALIZATIONS. EVERYTHING ACROSS FILTER, EVERYTHING ELSE. YOU CAN ALSO HAVE A PAGE FILTER, VISUAL FILTERS AND OTHER FILTERS, SO YOU HAVE A LOT OF FILTERS GOING ON. HOW CAN WE REPRESENT THIS? WELL, LET'S CONSIDER ONE CELL IN THIS REALIZATION. THE VALUE THAT YOU SEE HERE. AND THE MEASURE THAT WE USE TO COMPUTE THE SUM OF THE QUANTITY THAT YOU SEE IN THAT CELL IS ALWAYS THE SAME EXPRESSION. WHY THIS IS REDUCING THE QUANTITY TO 366 -- SORRY, 376 INSTEAD OF A BIGGER NUMBER? WELL, IF WE EVALUATE THE FILTER CONTEXT ACTIVE IN THAT PARTICULAR CELL, WE COULD DESCRIBE THIS THE FOLLOWING WAY. THE FILTER CONTEXT CAN BE REPRESENTED AS A SET OF FILTERS WHERE EVERYTHING FILTER IS JUST A LIST OF VALUES FOR ONE OR MORE COLUMNS. THE FIRST FILTER THAT WE HAVE OR SOMEONE CLICKED ON THE BAR CHART FOR THE YEAR 2007, SO THERE IS A FILTER THAT SAYS CALENDAR YEAR CAN BE ONLY 2007. AND THEN IF WE LOOK AT THE REPORT, THERE IS A SLICER THAT HAS A MULTIPLE SELECTION, SO THERE IS ANOTHER FILTER IN THE FILTER CONTEXT THAT SAYS EDUCATION CAN BE ONLY ONE OF THESE TWO VALUES. AND FINALLY, THE QUANTITY VALUE THAT WE HIGHLIGHTED IS IN A PARTICULAR ROW OF A METRICS, AND THIS MATRIX HAS A VALUE OF ONE BRAND IN THE THIRD ROW. SO THE THIRD FILTER IN THE FILTER CONTEXT SAYS BRAND MUST BE KONTOSO. SO THE WAY WE DESCRIBE THE FILTER CONTEXT IS THROUGH LIST OF VALUES OKAY? WHY IS THIS IMPORTANT? AS YOU SEE, I'M NOT SAYING THE FILTER CONTEXT IS A CONDITION BECAUSE THE FILTER CONTEXT DOES NOT INCLUDE CONDITIONS. CONTAINS ONLY FROM A LOGICAL POINT OF VIEW LIST OF VALUES. A FILTER IN THE FILTER CONTEXT IS A TABLE. IN OTHER WORDS, IF WE CONSIDER A FILTER CONTEXT WHERE WE HAVE TWO FILTERS, EVERY FILTER IS A TABLE THAT COULD HAVE ALSO MORE THAN ONE COLUMN. IMAGINE YOU WANT TO APPLY A FILTER THAT SAYS I WANT TO FILTER THE DECEMBER 2006 AND JANUARY 2007. IF YOU HAVE A COLOR MONTH WITH 12 MONTH NAMES, FILTERING BY MONTH IS NOT ENOUGH. YOU NEED TO COMBINE THE FILTER OF THE YEAR AND MONTH INTO SAME FILTER. BECAUSE IF YOU CREATE TWO DIFFERENT FILTERS, YOU SAY THE YEAR COULD BE 2006 AND SEVEN AND THE MONTH COULD BE JANUARY AND DECEMBER, WHAT DO YOU OBTAIN AS A RESULT? FOUR POSSIBLE MONTHS. YOU COMBINED THEM. WHEREAS IF YOU DEFINE A SINGLE FILTER THAT HAS TWO COLUMNS, YOU CAN SPECIFY ONLY CERTAIN PARTICULAR COMBINATION OF VALUES. BY THE WAY, POWER BI DOESN'T ALLOW YOU TO CREATE SUCH A FILTER. YOU CAN DO THAT IN EXCEL. IN EXCEL IF YOU CREATE A HIERARCHY THAT SAYS YEAR MONTH AND DAY, FOR EXAMPLE, YOU CAN SELECT ONLY ONE MONTH AND ONE YEAR AND ANOTHER MONTH IN ANOTHER YEAR. YOU THINK ABOUT THIS. BY DEFAULT, POWER BI DOESN'T HAVE A TOOL TO DO THAT. YOU DON'T HAVE A UI IN POWER BI THAT ALLOWS YOU TO SAY I WANT TO ONLY SEE DECEMBER 2006 AND JANUARY 2007. THERE IS A VISUALIZATION TO DO THAT IF YOU WANT. BUT DAX ALLOWS YOU TO DO THAT BUT REQUIRES THAT POINT TO HAVE A FILTER ATTACHED TO COLUMNS. NOW IN ORDER TO AVOID A CONFUSION BETWEEN TABLES AND FILTERS, BECAUSE A FILTER IS A TABLE WITH ROWS, WE CAN USE DIFFERENT NAMES. SO WE CAN CALL TOPPLE, THE LIST VALUE FOR A SET OF COLUMNS, LIKE THIS ONE. IN OTHER WORDS, ONE ROW IN ONE FILTER IS WHAT WE CALL TOPPLE. WE DON'T USE THIS NAME OFTEN, BUT THE TABLE THAT WE INJECT INTO THE FILTER CONTEXT IS SOMETHING THAT FROM HEREINAFTER I WILL CALL A FILTER. ACTUALLY, I ALREADY USED THIS NAME. BUT WHEN I SAY FILTER, ONE FILTER IS ONE TABLE. AND ONE FILTER CONTEXT IS A SET OF FILTERS, SO IS A SET OF TABLES. SO THE FILTER CONTEXT THAT YOU APPLY FOR A CALCULATION IS A SET OF TABLES THAT HAVE VALUES FOR ONE OR MORE COLUMNS. THE FILTER CONTEXT IS IMPORTANT BECAUSE APPLYING A FILTER CONTEXT TO A CALCULATION IS EXTREMELY FAST, OKAY? THIS IS IMPORTANT. AND THIS IS THE REASON WHY THIS IS THE PREFERRED WAY TO OBTAIN CALCULATIONS IN DAX. APPLYING A FILTER, APPLYING TWO FILTERS, MORE DEFINED FILTER CONTEXT AND THEN EXECUTING OUR CALCULATION. RATHER THAN GETTING A TABLE AND SCANNING THE TABLE ROW BY ROW, APPLYING THE FILTER ROW BY ROW. THIS IS SLOW. DON'T DO THAT. HOWEVER, THE EVALUATION CONTEXT IS MADE BY TWO PARTS. WE HAVE THE FILTER CONTEXT. WE HAVE THE ROW CONTEXT. WHAT IS THE ROW CONTEXT? WHENEVER YOU WRITE AN EXPRESSION, FOR EXAMPLE IN A CALCULATED COLUMN, WHAT IS A CALCULATED COLUMN? IF I OPEN MY EXAMPLE HERE, LET ME SEE IF I REMEMBER WHAT IS THE RIGHT -- OKAY, PERFECT. SO IF I GO HERE AND I GO IN ONE TABLE HERE, FOR EXAMPLE SAVES, AND IN THIS TABLE SAVES I CREATE HERE A NEW COLUMN. YOU CAN WRITE HERE, FOR EXAMPLE, YOU SEE THAT WE ARE QUANTITY, UNIT COST, NET PRICE. THE QUANTITY COULD BE TWO, THREE, FOUR, SO ACTUALLY WHAT IS THE AMOUNT FOR ONE LINE? I CAN WRITE A COLUMN CALLED LINE AMOUNT WHERE I WRITE QUANTITY MULTIPLIED BY NET PRICE. AS YOU SEE, INTELLISENSE SUGGESTED ME TO GET A COLUMN FROM THE CURRENT TABLE, AND I HAVE ACCESS TO ALL THE COLUMNS ON THIS TABLE BECAUSE WE HAVE A CURRENT ROLE WHICH WE CALL A ROW CONTEXT. THE ROW CONTEXT IS SOMETHING THAT EXISTS ONLY IN PARTICULAR CONDITIONS AND ALLOWS YOU TO WRITE A COLUMN REFERENCE, WHICH IS A DAX SYNTAX THAT SAYS A TABLE COLUMN, AND TABLE COLUMN HAS A MEANING WHEN YOU HAVE A ROW CONTEXT, AND THE MEANING IS GIVE ME THE VALUE OF THIS COLUMN IN THE CURRENT ROW OF THIS TABLE. WHAT IS THE CURRENT ROW? THE ROW CONTEXT. SO I WRITE THIS EXPRESSION, AND THIS EXPRESSION IS EVALUATED FOR EVERY ROW OF MY TABLE BECAUSE IN EACH ROW THERE IS A ROW CONTEXT THAT SAYS WHAT IS THE ROW THAT WILL RESOLVE THE COLUMN REFERENCES THAT YOU WRITE IN THIS EXPRESSION? NOW I WROTE MY EXPRESSION THIS WAY USING A CALCULATED COLUMN, BUT IN REALITY IT'S NOT A GOOD IDEA TO CREATE A CALCULATED COLUMN AND THEN COMPUTING A SUM OVER THAT BECAUSE I CAN OBTAIN THE SAME RESULT WITHOUT INCREASING THE SIZE OF THE DATA IN MEMORY BY USING A MEASURE. WHEN I DEFINE HERE, MY MEASURE SAYS, LET ME WRITE THIS MEASURE FROM SCRATCH. SO MY MEASURE COULD BE WRITTEN LIKE SUM X. SUM X HAS TWO ARGUMENTS. A TABLE AND FOR EACH ROW OF THIS TABLE I WANT TO EXECUTE AN EXPRESSION. SO IF I ITERATED THE TABLE, AND FOR EACH ROW I WRITE EXACTLY THE SAME FORMAT THAT I WOULD HAVE WROTE IN THE CALCULATED COLUMN, I CAN WRITE QUANTIFY, MULTIPLIED BY NET PRICE. ONCE AGAIN, BECAUSE THIS SYNTAX IS WRITTEN IN THE SECOND ARGUMENT OF SUM X AND THE SECOND ARGUMENT OF SUM X IS AN EXPRESSION THAT IS EXECUTED IN A ROW CONTEXT, I CAN USE COLUMN REFERENCES FOR THIS TABLE. NOW SOMETIMES SOMEBODY ASK, MARCO, HOW CAN WE KNOW WHAT ARE THE FUNCTIONS THAT HAVE ARGUMENTS EXECUTED IN A ROW CONTEXT IN DAX? I HAVE A BAD NEWS AND A GOOD NEWS. THE BAD NEWS IS THAT THIS INFORMATION IS NOT PROVIDED IN A CLEAR WAY IN INTELLISENSE OR IN THE DOCUMENTATION PROVIDED BY MICROSOFT. I CAN SAY, OH, THIS EXPRESSION SHOULD BE CALLED COMPUTED FOR EVERY ROW OF THE TABLE, BUT ACTUALLY, I DON'T HAVE A CLEAR DESCRIPTION OF THIS. THE GOOD NEWS IS THAT IF YOU GO TO THIS WEBSITE, DAXGUIDE, THIS WEBSITE HAS ADDITIONAL INFORMATION FOR EACH FUNCTION WHICH ALLOWS ME TO SAY, FOR EXAMPLE, IF I GO TO SUM X, YOU SEE THAT THE EXPRESSION, SUM X, IS EXECUTED IN A ROW CONTEXT. YOU SEE THIS GREEN BOTTOM HERE WHICH SAYS SIMPLY THAT THIS EXPRESSION IS EXECUTED IN A ROW CONTEXT WHICH IS EXACTLY WHAT I WANT TO SAY. WHEN YOU HAVE AN ARGUMENT IN A DAX FUNCTION THAT IS EXECUTING A ROW CONTEXT, YOU CAN USE COLUMN REFERENCES THAT OTHERWISE WOULD NOT BE VALID. OKAY? SO LET ME GO BACK HERE. AND LET ME GO BACK TO THE SLIDES. ONE SECOND. HERE WE GO. SO THE ROW CONTEXT SKUTS THE EVALUATION ROW BY ROW. FOR EVERY ROW IT EXECUTES THIS. SO WHY THE FEED FILTER CONTEXT AFFECTS THE FORMULA? WHAT IS GOING ON HERE? IF YOU THINK ABOUT THE FORMULA. THIS IS JUST THE ANIMATION THAT SHOWS YOU WHAT HAPPENS ROW BY ROW. IF YOU THINK ABOUT THE SUM OF THE TOUR, ROW AND FILTER CONTEXT, WHY THE TWO WORK TOGETHER, LET ME DESCRIBE THIS. SO HERE I HAVE THIS EXPRESSION, AND THIS IS AN AMOUNT. MY MEASURE AMOUNT IS EXECUTED IN MY REPORT HERE, IN EACH CELL HERE, AND EACH CELL HERE AS A DIFFERENT FILTER CONTEXT. SO THINK ABOUT THIS. WHAT IS THE FILTER CONTEXT OF THIS CELL? LET ME SELECT 2007 AND GREEN. WHAT IS THE FILTER CONTEXT OF THIS CELL? WELL, THE WAY I DESCRIBE THE FILTER CONTEXT IS EXACTLY THE FOLLOWING, AND I LIKE TO USE THIS APPROACH HERE. MY FILTER CONTEXT IS THIS: IF I WANT TO THINK ABOUT WHAT THE THE FILTER CONTEXT, I HAD A SLICER AT TWO VALUES, ONE AND TWO. THIS ONE FILTER IN THE FILTER CONTEXT. THE SECOND SLICER IS 2007, THAT WAS THE YEAR. THE THIRD FILTER IN THE FILTER CONTEXT WAS, IF I REMEMBER WELL, THE COLOR GREEN. OKAY? HOW HOW THESE THREE FILTERS AFFECT MY CALCULATION, MY SUM X? WELL, IF I GO BACK TO MY DEFINITION OF THE SUM X, THE MEASURE SAYS AMOUNT HERE HAS A TABLE EXPRESSION, AND THE TABLE EXPRESSION IN THIS CASE IS SIMPLY A TABLE REFERENCE, BUT THIS TABLE DOES NOT SHOW, DOES NOT RETRIEVE ALL THE ROWS OF THE SAYS TABLE. IT RETRIEVES ONLY THOSE ROWS IN SAYS THAT ARE VISIBLE THROUGH THE CURRENT FILTER CONTEXT. SO IT IS SAYS THAT IS AFFECTED BY THE FILTER CONTEXT, WHEREAS WHATEVER I WROTE IN THE SECOND ARGUMENT IS EXECUTED IN THE ROW CONTEXT AND THAT POINT DOESN'T MATTER BECAUSE I DON'T HAVE AGGREGATIONS. THE FILTER CONTEXT AFFECTS AGGREGATIONS, TABLE EXPRESSIONS, NOT COLUMN REFERENCE. A COLUMN REFERENCE IS VALID ONLY WHEN YOU HAVE A ROW CONTEXT, OKAY? SO THIS IS THE WAY WE CAN RECAP VERY QUICKLY HOW ROW CONTEXT AND FILTER CONTEXT WORK. NOW WHEN I HAVE A MODEL, FOR EXAMPLE, I HAVE A MODEL WITH TABLES AND RELATIONSHIPS, AND THESE TABLES AND RELATIONSHIPS HAVE AN EFFECT TO MY MODEL, AND SOMETIME, HOWEVER, SOME BEHAVIOUR IS NOT TOTALLY CLEAR IF WE DON'T INTRODUCE THIS CONCEPT, WHICH IS THE CONCEPT OF THE EXPANDED TABLE. SO BEFORE GOING HERE, LET ME GO BACK ONE SECOND TO THE MODEL BECAUSE I WANT TO HIGHLIGHT. IN THIS MODEL THERE IS THIS SITUATION. THIS IS THE MODEL THAT I USED IN THE EXAMPLE WE MADE SO FAR. AND YOU SEE THAT I HAVE A CENTRAL TABLE HERE WHICH IS CALLED -- OH, LET'S SEE. THIS ONE. THIS TABLE IS CALLED SAY, AND I WAS ITERATING SAFES, BUT THEN I WAS APPLYING A FILTER OVER CALENDAR YEAR IN DATE AND I WAS APPLYING A FILTER OVER COLOR. NOW WHY IF I APPLY FILTERS IN DIFFERENT TABLES THESE FILTERS AFFECT MY CALCULATION? BECAUSE A FILTER APPLIED TO THE FILTER CONTEXT AUTOMATICALLY PROP GAITS TO OTHER TABLES IN THE MODEL THROUGH RELATIONSHIPS. A RELATIONSHIP IS A TOOL IN DAX THAT TRANSFERS A FILTER BETWEEN TABLES. AND THIS TRANSFER HAPPENS ONLY FOLLOWING THE DIRECTION THAT YOU ENABLED HERE. BY DEFAULT, AND AS A BEST PRACTICE, WE ONLY TRANSFER FILTER FROM ONE TO MANY. WE CAN ENABLE THE BIDIRECTIONAL FILTER, BUT IT SHOULD BE DONE IN ALL PARTICULAR CASES WHERE WE KNOW EXACTLY WHAT WE'RE GOING TO DO, AND SO BY DEFAULT, WHEN YOU APPLY A FILTER OVER THE PRODUCT TABLE, FOR EXAMPLE, THE FILTER IS AUTOMATICALLY PROPAGATED TO THE SAFES TABLE. SO THIS IS THE DEFAULT BEHAVIOUR OF THE FILTER CONTEXT. THE FILTER CONTEXT AUTOMATICALLY PROPAGATE THROUGH RELATIONSHIPS. BUT YOU SEE THAT THE PRESENCE OF THIS TABLE SOMETIMES HAS SOME EFFECT IN OUR CALCULATION, AND WHAT IS THIS EFFECT? LET ME DESCRIBE. WHAT WE HAVE IS THIS CONCEPT. EVERY TABLE THAT WE HAVE IN THE MODEL HAS A DEFINITION OF A BASE TABLE IN THE MODEL. SO THE BASE TABLE IN THE MODEL IS EXACTLY THE TABLE THAT YOU LOAD IN THE MODEL. BUT WHENEVER YOU CREATE A MANY-TO-ONE RELATIONSHIP BETWEEN ONE TABLE AND OTHER TABLES, YOU DEFINE A POTENTIAL JOIN BETWEEN YOUR BEST TABLE AND OTHER TABLES. SO IF YOU LOOK AT THE TABLE AND YOU IMAGINE TO FLATTEN THE TABLE JOINING ALL THE OTHER TABLES THAT YOU CAN REACH THROUGH MANY-TO-ONE RELATIONSHIPS, WHAT YOU OBTAIN AS A RESULT IS WHAT IS CALLED, IN THE DAX WORD, AS AN EXPANDED TABLE. SO THE PRESENCE OF THE RELATIONSHIP BETWEEN, FOR EXAMPLE, PRODUCT AND PRODUCT SUBCATEGORY, DEFINES THESE TWO CONCEPTS. PRODUCT IS A BASE TABLE MADE ONLY BY THE -- OF PRODUCT, BUT THE EXPANDED TABLE PRODUCTS INCLUDES ALL THE COLORS OF PRODUCTS PLUS ALL THE COLORS OF SUBCATEGORY. JUST LIKE YOU DO A LEFT JOIN USING THE RELATIONSHIP. AND WHEN WE CONSIDER THIS, THIS DOESN'T HAPPEN ONLY FOR THE FIRST RELATIONSHIP. IF YOU HAVE PRODUCT, SUBCATEGORY, CATEGORY, THREE TABLES, THE EXPANDED TABLE PRODUCT INCLUDES ALL THE COLORS OF PRODUCT PLUS ALL THE COLORS OF SUBCATEGORY PLUS ALL THE COLORS OF CATEGORY. WHY THIS IS IMPORTANT? THIS IS IMPORTANT BECAUSE EVERY TIME YOU APPLY A FILTER INTO THE FILTER CONTEXT YOU CAN APPLY A FILTER FILTERING ONE COLUMN, TWO COLUMNS, THREE COLUMNS, AND YOU FILTER ONLY THE COLUMNS THAT YOU FILTER. BUT WHENEVER YOU SPECIFY A TABLE AS A FILTER IN THE FILTER CONTEXT, YOU ALWAYS REFERENCE THE EXPANDED TABLE, NOT JUST THE BASE TABLE. >> [INDISCERNIBLE]. >> YES? >> WHY WOULD YOU CREATE A RELATIONSHIP [INDISCERNIBLE] WHEN YOU SAY ONE TO MANY -- [INDISCERNIBLE] WHAT IS THE BEST OPTION? SINGLE OR BOTH? >> OKAY. I REPEAT THE QUESTION JUST FOR THE RECORDING. THE QUESTION IS: WHEN I HAVE A RELATIONSHIP IN THE MODEL, LIKE I HAVE SHOWN HERE, THE RELATIONSHIP IS DEFINED THIS WAY AS A ONE-TO-MANY. AND YOU SEE THAT THE CROSS-FILTER DIRECTION COULD BE SINGLE OR BOTH. BY DEFAULT, WHEN YOU CREATE A NEW RELATIONSHIP, A ONE-TO-MANY RELATIONSHIP IS ALWAYS SINGLE, AND MY STRONG SUGGESTION, KEEP IT SINGLE. THERE ARE CASES WHERE YOU MIGHT WANT TO ENABLE THE BIDIRECTIONAL FILTER FOR PARTICULAR REASONS. THE BEST PRACTICE IS TO NOT ENABLE TO CROSS FILTER -- AS A BIDIRECTIONAL IN THE MODEL UNLESS YOU KNOW EXACTLY WHAT YOU'RE DOING AND WHAT ARE THE SIDE EFFECTS OF THAT, BECAUSE USUALLY YOU WANT TO ENABLE A BIDIRECTIONAL FILTER FOR SOME SPECIFIC CALCULATIONS, AND YOU CAN OBTAIN THIS RESULT BY USING A SPECIFIC FUNCTIONING -- STATEMENT WHICH IS CROSS-FILTER. CROSS-FILTER IS A WAY THAT ENABLES A BIDIRECTIONAL FILTER FOR A SPECIFIC CALCULATION. THIS WAY YOU OBTAIN THE EFFECT THAT YOU WANT FOR THAT CALCULATION WITHOUT HAVING SOMETIMES UNDESIRED SIDE EFFECTS ON OTHER CALCULATION. IF YOU ENABLE THE BIDIRECTIONAL FILTER IN THE MODEL, EVERY CALCULATION WILL BE AFFECTED BY THAT. AND YOU MIGHT HAVE TWO KIND OF PROBLEMS. ONE IS PERFORMANCE AND THE OTHER IS UNDESIRED NUMBERS THAT YOU OBTAIN AS A RESULT. SO WHY I MENTIONED THIS THING ABOUT THE EXPANDED TABLE, BECAUSE THIS IS SOMETHING THAT WE WILL SEE HOW WE CAN MANIPULATE THE FILTER CONTEXT. SO I SHOWN YOU WHAT THE FILTER CONTEXT IS FROM A CONCEPTION POINT OF VIEW, BUT THE POWER OF DAX IS THE ABILITY TO MODIFY THE FILTER CONTEXT BEFORE EXECUTING SOME ACTUAL CALCULATION. IF YOU WANT TO GET PERFORMANCE IN DAX, YOU HAVE TO MANIPULATE THE FILTER CONTEXT AND THEN EXECUTE THE CALCULATION BECAUSE MANIPULATING THE FILTER CONTEXT BEFORE THE CALCULATION IS THE MORE EFFICIENT WAY TO ACTUALLY QUERY THE DATA THAT YOU HAVE IN THE MODEL. IF YOU DO SOMETHING DIFFERENT, YOU MIGHT END UP IN HAVING A SLOW PERFORMANCE BECAUSE YOU BASICALLY APPLY MORE COMPLEX AND LONG CALCULATIONS OVER DATA THAT IS MATERIALIZED IN MEMORY, LIKE IMAGINE YOU CREATE MORE TEMPORARY TABLES THAT ARE UNNECESSARY. WHEREAS IF YOU MANIPULATE THE FILTER CONTEXT, YOU PROBABLY REDUCE THIS REQUIREMENT, AND YOU OBTAIN BETTER PERFORMANCE AND MORE CONTROL. NOW CALCULATE WORKS THIS WAY. WHEN YOU WRITE, FOR EXAMPLE, A CALCULATE STATEMENT FOR A MEASURE CALLED RED SAFES, HOW DO YOU WRITE RED SAFES? I CAN WRITE THE CALCULATION THIS WAY. SO IF MY SUM X SAYS QUANTITY MULTIPLIED BY NET PRICE IS MY CALCULATION OF THE SAFES AMOUNT, I CAN EMBED THIS CALCULATION. THE FIRST ARGUMENT IN CALCULATOR, AND CALCULATOR CAN APPLY ONE OR MORE FILTERS TO THE FILTER CONTEXT BEFORE EXECUTING THE FIRST ARGUMENT. SO THE FIRST THING IS THAT CALCULATOR MUST BE READING IN A DIFFERENT ORDER. IT HAS TO USUALLY READ THE FILTERS BEFORE THE FIRST ARGUMENT. SO THE FIRST ARGUMENT IS THE LAST ONE TO BE EVALUATED. >> AND THE CALCULATION THAT YOU SEE IN THE SLIDE IS APPLYING THE FILTER TO THE COLOR OF THE TABLE AND THEN IT EVALUATES THE CALCULATION. BUT CALCULATOR, MY MEASURE, IS EXECUTED IN A FILTER CONTEXT BECAUSE WE SAID, OH, WHENEVER I -- AND I USED MY MEASURE IN MY MODEL. ACTUALLY, MY MEASURE RECEIVES AN INITIAL FILTER CONTEXT DEFINED BY MY SLICERS, MY OTHER VISUALIZATIONS AND SO ON. SO WE CALLED THIS THE OUTER FILTER CONTEXT, SO THE OUTER FILTER CONTEXT IS THE CONTEXT WE HAVE OUTSIDE OF CALCULATOR. CALCULATOR CAN GENERATE AN INNER FILTER CONTEXT WHICH IS THE RESULT OF THE OUTER FILTER CONTEXT MODIFIED BY THE ARGUMENTS THAT YOU WRITE IN THE SECOND, THIRD AND FOLLOWING ARGUMENTS OF CALCULATOR. AT THIS POINT IN THIS INNER FILTER CONTEXT, CALCULATOR EXECUTES THE EXPRESSION, IN THIS CASE SUM X IS AN ITERATOR THAT FOR EACH ROW OF SAFES EVALUATED IN THE ROW CONTEXT OF HOW MANY ROWS. THOSE ROWS THAT ARE VISIBLE IN THE INNER FILTER CONTEXT. SO YOU CAN SEE THAT STEP BY STEP WE CAN EXAMINE EXACTLY WHAT IS GOING ON. NOW IN THIS CASE I WROTE A MEASURE THAT SAYS PRODUCT RECALL RED, WHICH MEANS THAT I WANT TO ADD INTO THE FILTER CONTEXT A FILTER OVER THE COLUMN COLOR OF THE TABLE PRODUCT, AND THIS FILTER THAT I CREATE HAS ONLY ONE VALUE, RED. THE RESULT OF MY NEW FILTER IN THE FILTER CONTEXT IS NOT A CONDITION, A LOGICAL CONDITION, BUT IS A TABLE, A FILTER IS A TABLE. IN THIS CASE OF THE TABLE IT HAS ONE ROW, ONE COLUMN. BUT IF YOU WRITE PRODUCT COLOR IN RED BLUE-GREEN, THIS STATEMENT GENERATES A TABLE THAT HAS THREE ROWS, OKAY? SO MY NEW FILTER IS ADDED TO THE FILTER CONTEXT, CHANGING THE WAY THE EVALUATION WILL BE COMPUTED. SO FILTERS ARE TABLES, AND WE USUALLY USE THIS SYNTAX, WHICH IS A SYNTAX THAT WE TECHNICALLY CALL A PREDICATE. A PREDICATE IS A LOGICAL CONDITION, A BULLION CONDITION, IF YOU PREFER. BUT DAX, IN ORDER TO EXECUTE THIS CODE, ACTUALLY GENERATES THIS CODE INSTEAD. DAX TRANSFORMS YOUR COMPLEX SYNTAX INTO ANOTHER DAX EXPRESSION THAT GENERATES A TABLE THAT HAS ONE COLUMN, PRODUCT COLOR, AND AS MANY ROWS AS THOSE ROWS THAT SATISFY THE FILTER THAT YOU WROTE. A FILTER IS A TABLE. WHENEVER YOU WRITE A PREDICATE, YOU ARE JUST WRITING A PREDICATE THAT IS TRANSLATED INTO AN EQUIVALENT TABLE EXPRESSION GENERATING A TABLE WITH A LIST OF VALUES THAT YOU WANT TO FILTER. IF I WRITE PRODUCT COLOR RECALL RED, IT'S EASY. BUT LET ME JUST -- IF I HAVE TIME, YES. LET ME SHOW YOU ONE THING. LET'S DO AN EXERCISE TOGETHER. NOW I HAVE THIS VISUALIZATION. FOR EXAMPLE, LET'S GO HERE. THIS IS MY PRODUCT. AZURE. OKAY, FANTASTIC. SO MY SALES AMOUNT IS MY CALCULATION THAT WE HAVE SEEN BEFORE. SO QUANTITY MULTIPLIED BY NET PRICE. IF I CLICK HERE, MEASURE, RED SAFES, OKAY, RED SAFES, USUALLY THE WAY WE CREATED THE MEASURES IS NOT BY WRITING SOME X BLAH, BLAH, BLAH, BUT THE EASIEST WAY TO CREATE A MEASURE IS TO CALL ANOTHER MEASURE, AMOUNT, APPLYING SOME FILTER. BECAUSE THIS WAY WE RE-USE AN EXISTING BUSINESS LOGIC IN OUR MEASURE. AND FOR EXAMPLE RIGHT HERE I WRITE COLOR EQUAL TO RED. WHAT HAPPENS IF I DO THIS? THINK ABOUT THIS. IF I INCLUDED THIS RED SAFES HERE HERE, THIS IS INTERESTING. LOOK AT THE RESULT. THIS IS ALWAYS BLANK, AND HERE WE ALWAYS HAVE THE SAME VALUE OF RED. WHY? WHAT IS HAPPENING? LET ME DESCRIBE. THINK ABOUT THIS. WE HAVE -- LET'S CONSIDER THIS CELL. WHAT IS THE FILTER CONTEXT OF THE CELL? LET ME DRAW THIS. SO WE HAVE ADVENTURE WORKS IN BLUE. SO THE FILTER CONTEXT FOR THE
CELL IS EXACTLY THIS ONE. FILTER CONTEXT, ONE FILTER IS BLUE. WHICH IS THE COLOR. AND THE FILTER IS I THINK BRAND THAT WAS ADVENTURE WORKS. THIS IS THE INITIAL FILTER CONTEXT. ANY CALCULATOR CREATED A NEW FILTER: COLOR RED. NOW THIS FILTER GOES INTO THE FILTER CONTEXT, BUT BECAUSE THERE IS ALREADY A FILTER OVER THE PRODUCT COLOR COLUMN, THE EXISTING FILTER OVER THE SAME COLUMN IS REMOVED, AND A NEW FILTER REPLACES THE PREVIOUS FILTER. WHEN YOU APPLY A FILTER TO THE FILTER CONTEXT, YOU OVERRIDE AN EXISTING FILTER OVER THE SAME COLUMN, BECAUSE WE FILTER BY COLUMN. SO THE RESULT OF THIS IS WRITE ADVENTURE WORKS, RIGHT? SO IF WE GO BACK TO OUR REPORT, YOU SEE THAT IN EVERY CELL WE START WITH THE TWO FILTERS, ONE BRAND AND ONE COLOR, BUT WE ALWAYS OVERRIDE THE COLOR USING OUR RED COLOR. SO WHY WE HAVE BLANK HERE? WE DO THE SAME. WE DO THE SAME. WE REPLACE THE CURRENT COLOR WITH RED, KEEPING THE CURRENT BRAND, AND GUESS WHAT? WHAT IS THE VALUE OF RED FOR A DATUM? YOU DON'T SEE RED. THERE ARE NO SALES FOR PRODUCTS THAT HAVE A COLOR RED AND THE BRAND RED. SO IT IS WORKING. I'M REPLACING A FILTER WITH ANOTHER FILTER. WHEN YOU APPLY A COLUMN FILTER, YOU REPLACE THAT FILTER WITH ANOTHER FILTER. WHAT IF THIS IS NOT WHAT YOU WANT TO DO? IN CASE I DON'T WANT TO OVERWRITE THE FILTER, I CAN USE KEEP FILTERS. KEEP FILTERS SAYS I DON'T WANT TO OVERWRITE THE FILTER. I WANT TO APPLY THIS FILTER, KEEPING ANY EXISTING FILTER. IF YOU DO THIS, YOU SEE THAT THE RESULT IS DIFFERENT BECAUSE NOW I HAVE THE VALUE OF RED ONLY HERE, ALL THE OTHER VALUES ARE BLANK. WHY? BECAUSE IF I GO BACK TO MY FILTER CONTEXT, ONLY THE LAST THING THAT THIS REMOVAL DIDN'T HAPPEN, SO MY FINAL FILTER CONTEXT IS THE -- SORRY. MY FINAL FILTER CONTEXT IS THE FOLLOWING. I HAD A FILTER CONTEXT MADE BY, FOR EXAMPLE, BLUE AND ADVENTURE WORKS. SO THIS WAS THE COLOR, THIS WAS THE BRAND. MY NEW FILTER IS RED, AND WHAT IS THE PROBLEM? HOW MANY ROWS IN THE SAFE TABLES SATISFY BOTH FILTERS? NONE. A PRODUCT CAN BE EITHER RED OR BLUE. SO BASICALLY WHEN YOU HAVE MULTIPLE FILTERS IN THE SAME FILTER CONTEXT FOR THE SAME COLUMN IT'S LIKE YOU HAVE AN END CONDITION BETWEEN THESE FILTS. KEEP THE EXISTING FILT BECAUSE YOU DON'T WANT TO REMOVE IT, AND SO THIS IS ONE OF THE TOOLS THAT YOU HAVE IN DAX TO CONTROL WHAT HAPPENS INTO THE FILTER CONTEXT. WHAT IS MY NOTE HERE. OKAY. SO INTERNALLY THIS IS WHAT HAPPENS. AND THE PROBLEM IS IF I HAVE A CONDITION THAT REQUIRES TWO DIFFERENT COLUMNS, WELL, A CONDITION THAT REQUIRES TWO DIFFERENT COLUMNS IS A CONDITION THAT REQUIRES MORE THAN ONE COLUMN IN THE FILTER. AND IF YOU TRY TO WRITE THIS CONDITION MANUALLY, YOU GET AN ERROR. FOR EXAMPLE, LET ME GO BACK TO MY EXAMPLE, AND LET ME SHOW YOU A COUPLE OF OTHER EXAMPLES THAT I I COULD WRITE. HERE, LET ME REMOVE THIS AND THIS. I COULD WRITE A MEASURE CALLED BIG SALES. AND BIG SALES IS EQUAL TO A SUM X OF SET AMOUNT -- SORRY, NOT SUM. CALCULATE OF SALES AMOUNT, AND LET'S SAY MY INITIAL DEFINITION OF -- SORRY. YOU DON'T WANT -- MY INITIAL SALES IS ANY TRANSACTION THAT HAS AT LEAST ANYTHING TWO AS A QUANTITY. SO LET'S SAY THE QUANTITY MUST BE GREATER -- SALES QUANTITY MUST BE GREATER THAN ONE. SO IF I SHOW YOU THIS VALUE AND I ASK YOU TO UNDERSTAND WHAT I'M DOING, WHAT IS THE FILTER CONTEXT HERE? THE INITIAL FILTER CONTEXT HAS A TWO FILTERS, RIGHT, WHICH ARE ADVENTURE WORKS AND BROWN. SO LET'S DO THIS. SO I GO TO MY WHITEBOARD AND I DRAW MY FILTER CONTEXT THAT IS MADE BY TWO FILTERS. ONE IS ADVENTURE WORKS AND THE OTHER IS BROWN. THIS IS THE COLOR. THIS IS THE BRAND. BUT MY CALCULATE STATEMENT CREATED A NEW FILTER OVER SALES QUANTITY. WHAT SHOULD I WRITE HERE? THINK ABOUT THIS. I WROTE SALES QUANTITY GREATER THAN ONE. THIS TIME I DIDN'T WRITE EQUAL TO, RIGHT? AND THE PROBLEM IS THAT THIS FILTER IS TWO, THREE, FOUR. A FILTER CAN BE EXPENSIVE. A FILTER EQUAL TWO IS ALWAYS A CHEAP FILTER, BUT THE FILTER GREATER THAN, OTHER THAN ZERO, EXPENSIVE. NOW THE ENGINE USUALLY IS NOT ENOUGH TO OPTIMIZE LATER THIS, BUT FROM A LOGICAL POINT OF VIEW, THIS IS THE WAY YOU HAVE TO THINK ABOUT THE FILTER CONTEXT, BECAUSE SOMETIMES THE FILTER IS MATERIALIZED THIS WAY AND CAN BE EXPENSIVE. I'M LUCKY. I KNOW THAT IN MY QUANTITY COLUMN I HAVE ONLY TWO, THREE, FOUR. THAT'S FINE, OKAY? AND IF MY CONDITION IS BY QUANTITY, IT JUST WORKS. AND THE NUMBER IS SMALLER THAN THE OTHER. SO FAR SO GOOD. NOW WHILE I WAS SAYING I MAY WANT TO USE KEY FILTERS, IMAGINE THAT SOMEONE APPLIES A FILTER OVER QUANTITY. SO IF I SHOW THAT IN COLUMNS HERE, I APPLY FILTER OVER QUANTITY IN THE PAGE FILTER, FOR EXAMPLE, SO I GO HERE AND QUANTITY HAS TO BE GREATER THAN -- SORRY. LESS THAN FOUR. OKAY? IF I APPLY THIS FILTER, LOOK AT WHAT HAPPENS TO SALES AMOUNT. SALES AMOUNT WILL CHANGE. BIG SALES WILL NOT CHANGE, RIGHT? WHICH IS QUITE STRANGE, BECAUSE AT THIS POINT WE MIGHT HAVE A CONDITION -- IMAGINE I HAVE THREE HERE. I DON'T HAVE ANY STRANGE CONDITION HERE, BUT IN SOME CASE I COULD SEE NUMBERS THAT LOOK STRANGE. WHAT CAN I DO TO APPLY THIS FILTER TO BIG SALES TOO? WELL, BIG SALES -- KEEP FILTERS. IF I DON'T USE KEEP FILTERS, MY FILTER SAYS QUANTITY GREATER THAN ONE WILL OVERWRITE THE FILTER, THE PAGE 11 FILTER. WHEREAS IF I USE KEEP FILTERS, NOW I KEEP BOTH AND I'M SURE THAT BIG SALES WILL BE ALWAYS A SUBSET OF THE EXISTING FILTERS. SO THIS IS SOMETHING THAT YOU HAVE TO KEEP IN MIND. BUT USUALLY FOR A SINGLE COLUMN FILTER, THIS IS NOT A BIG ISSUE. SO LET ME INTRODUCE THE OTHER PROBLEM. IN REALITY I GO TO MY MANAGER AND MY MANAGER SAYS, NO, MARCO, THIS IS NOT GOOD. I WANT TO FILTER THOSE TRANSACTIONS THAT HAVE AN AMOUNT THAT IS GREATER THAN 1, 000. WHAT IS THE AMOUNT? OKAY, THE AMOUNT IS SOMETHING I COULD OBTAIN WITH A MEASURE, BUT IT'S EXPENSIVE, SO THE BEST WAY TO DO THAT COULD BE TO REPRODUCE THE SALES NET PRICE MULTIPLIED BY SALES QUANTITY GREATER THAN 1, 000, FOR EXAMPLE. I COULD SAY, MARCO, MY MANAGER SAYS, MARCO, THE TRANSACTION THAT FOR EACH ROW HAS A VALUE GREATER THAN 1, 000 IS WHAT WE CONSIDER A BIG SALE. BUT IN ORDER TO SAVE MEMORY, I DIDN'T INCLUDE THE LINE AMOUNT COLUMN BECAUSE IF I HAD A LINE AMOUNT COLUMN, IT WOULD BE A FILT OVERER OVER A SINGLE PROBLEM EASY. GUESS WHAT, I CANNOT DO THE FILTERS OVER TWO COLUMNS. I GET AN ERROR. HOWEVER, YOU SAY, WAIT, I CAN WRITE A FILTER PROVIDING A ROW CONTEXT THAT MAKES THIS FILTER VALID. BUT WHAT HAPPENS IF YOU WROTE THIS? THIS COULD WORK. THE FILTER WORKS AND PRODUCES A RESULT, BUT LET ME DESCRIBE THE FILTER CONTEXT FOR THIS CELL. ADVENTURE WORKS BLUE. THAT'S THROW THIS. WHAT IS THE OUR FILTER FOR SALES. SALES IS A TABLE. IT HAS ALL THE COLORS OF THE SALES TABLE PLUS ALL THE COLORS OF -- IF THIS IS SALES, THEN WE HAVE PRODUCT. THEN WE HAVE DATE, BECAUSE EVERY TIME YOU REFERENCE A TABLE IN THE FILTER CONTEXT, YOU'RE REFERENCING THE EXPANDED TABLE. MOREOVER, HOW MANY ROWS YOU HAVE IN MANY. YOU SEE THE PROBLEM? FILTERING A TABLE IS A WAY IN DAX THAT DESCRIBES A FILTER THAT IS VERY, VERY EXPENSIVE. THERE ARE VERY RARE CASES WHERE YOU WANT TO DO THAT. MOST OF THE TIMES YOU WANT TO -- YOU CAN OBTAIN THIS RESULT BY SIMPLY FILTERING BY NOT USING THE TABLE BUT JUST THE COLUMNS THAT YOU HAVE. ALL SALES NET PRICE AND THE SALES QUANTITY. NOW I CREATE A TABLE THAT HAS ONLY THE EXISTING COMBINATIONS OF NET PRICE AND QUANTITY. I FEED THAT THROUGH THIS TABLE. IT HAS ONLY TWO COLUMNS AND I OBTAIN ONLY THE EXISTING COMBINATION OF THOSE COLUMNS THAT CAN PRODUCE THIS NUMBER. IF I DO THAT, MY CALCULATION PRODUCES APPARENTLY THE SAME RESULT, BUT AT WHICH PRICE? LET ME DESCRIBE. THE INITIAL FILTER CONTEXT IS ALWAYS THE SAME. FILTER ONE, FILTER TWO. ONE IS BRAND, ONE IS COLOR, OKAY? BUT THEN MY NEW FILTER HAS ONLY TWO COLUMNS, QUANTITY AND PRICE. AND I HAVE ONLY THE LIST OF THE UNIQUE COMBINATION THAT SATISFY THIS CONDITION. THIS FILTER IS CHEAPER AND IS MORE LET'S SAY DAX FRIENDLY BECAUSE WE FILTER COLUMNS NOT TABLES. IT'S ALWAYS BETTER TO EXPRESS A FILTER THIS WAY. >> CAN YOU ACCOMPLISH THAT USING ALL . . . [INDISCERNIBLE] DID I SEE THAT RIGHT? >> SORRY, I DON'T UNDERSTAND THE QUESTION. >> WHERE YOU HAVE ALL RIGHT THERE. >> YES. >> THAT'S THE FUNCTION THAT ACCOMPLISHED THE MORE EFFICIENT CALCULATION HERE? >> IT DEPENDS. THE QUESTION IS IS THIS THE MORE EFFICIENT -- USUALLY YES. BECAUSE EVEN THOUGH YOU MAY SAY, BUT MARCO, THIS WAY I GET ALL THE COMBINATION OF NET PRICE AND QUANTITY EVEN THOUGH I HAVE AN EXISTING FILTER IN THE TABLE. YOU ARE RIGHT, BUT REMEMBER THAT YOU WILL EXECUTE THIS EVALUATION FOR EVERY CELL OF THE REPORT. AND EVERY CELL OF THE REPORT MIGHT HAVE A DIFFERENT FILTER CONTEXT WHICH WOULD REQUIRE TO GENERATE THIS TABLE FOR EVERY CELL. IF I USE ALL, I IGNORE THIS FILTER, AND SO THIS FILTER IS CREATED ONLY ONCE, AND IT IS APPLIED OVER THE EXISTING FILTER CONTEXT, BUT THERE IS ONLY ONE PARTICULAR PROBLEM THAT YOU HAVE TO CONSIDER. IF I READ THE FILTER THIS WAY, I OVERWRITE ANY EXISTING FILTER ON NET PRICE OR QUANTITY. AND I HAVE A FILTER I DON'T WANT TO OVERWRITE. IF YOU USE A FILTER TABLE, LIKE I DID BEFORE, YOU INHERIT THE EXISTING FILTER, AND IN THAT FILTER YOU APPLY THE FILTER. NOW IN APPLYING A FILTER THAT OVERRIDES THE FILTER. SO IF I WANT TO MAKE SURE THAT I OBTAIN THE SAME SEMANTICAL EQUIVALENT RESULT OF THE PREVIOUS TABLE IN THIS CASE I HAVE TO USE KEEP FILTERS BECAUSE IF I DON'T USE KEEP FILTERS THE EXISTING FILTER OR QUANTITY WOULD BE REMOVED WITH THE SYNTAX, WHEREAS IT WAS KEPT IN THE FILTER CONTEXT IN THE OTHER SYNTAX. THIS IS JUST TO EXPLAIN THAT IF I'M CREATING A NEW MEASURE FROM SCRATCH, I DON'T CARE BECAUSE I CAN FIX IT LATER. BUT IF YOU CHANGE AN EXISTING MEASURE AND YOU DON'T WANT TO BREAK EXISTING REPORTS, YOU HAVE TO MAKE A TRANSFORMATION THAT IS SEMANTICALLY EQUIVALENT, LIKE THIS ONE. OKAY? NOW LET ME GO BACK TO THE SLIDES. HERE WE GO. OKAY SO WHAT WE HAVE SEEN IS THAT I COULD FILTER A TABLE, BUT THE TABLE FILTERS ARE BAD FOR THE REASON THAT I TRIED TO DESCRIBE IN THE VISUAL WAY. BECAUSE IF YOU THINK ABOUT THE TABLE, THE TABLE IS BIG, IS HUGE, IS EXPENSIVE. WE CAN FILTER USING A TABLE THAT HAS ONLY THE COLORS THAT WE NEED. YES, CREATING THIS TABLE COULD BE EXPENSIVE. IT DEPENDS ON THE GRANULARITY OF THE CONTENTS OF THE TABLE, BY THE WAY, BUT USUALLY IT'S THE CHEAPER WAY. WE COULD CONSIDER ALTERNATIVES LIKE USING SUMMARIZE OR OTHER TECHNIQUES, BUT IT'S SOMETHING THAT I WILL DO IN THE VERY, VERY SPECIFIC CONDITIONS. OTHERWISE THIS IS USUALLY THE BEST SYNTAX ALSO BECAUSE THE ENGINE HAS BEEN OPTIMIZED FOR THESE SYNTAXES. SO BASICALLY MOST OF THE TIMES I GET A GOOD RESULT IN TERMS OF PERFORMANCE. SO THIS IS A COLUMN FILTER. IF I EXPLICITLY REFERENCED ONE, TWO, THREE OR MORE COLORS, IS A COLOR FILTER. BUT WHEN YOU WRITE A TABLE NAME, YOU'RE WRITING AN EXPANDED TABLE FILTER THAT, AS I SAID, IT COULD BE MUCH MORE COMPLEX. OKAY? NOW I CAN MANAGE TO NOT ONLY ADD NEW FEATURES, I COULD APPLY CHANGES TO THE FILTER CONTEXT. FOR EXAMPLE, REMOVING FILTERS. AND THERE ARE OTHER FUNCTIONS THAT CAN BE USED ONLY IN CALCULATE THAT ARE CALLED THE CALCULATE MODIFIERS. I USED ONE. KEEP FILTERS CHANGES THE BEHAVIOUR OF HOW THE FILTER IS APPLIED. IN CROSS FILTER, CHANGE THE CURRENT RELATIONSHIP AND THE DIRECTION OF THE PROPAGATION OF A FILTER CONTEXT IN THE FILTER CONTEXT. SO THESE ARE SPECIFIC FILTER MOIFRS THAT CHANGE THE BEHAVIOUR OF CALCULATE. BUT THERE ARE A SET OF FUNCTIONS THAT ARE ALWAYS FILTER MODIFIERS WHEN THEY ARE EXECUTED AS THE ONLY FUNCTION IN A FILTER ARGUMENT OF CALCULATE, AND ARE ALL THE FUNCTIONS THAT START WITH "ALL. " FOR EXAMPLE, WHEN I WRITE THIS SYNTAX, ALL REMOVES THE FILTER THAT EXIST IN A COLUMN. WHAT DOES IT MEAN? IF I WRITE THIS SYNTAX, ALL COLUMN, I REMOVE A FILTER FROM THE FILTER CONTEXT. I DON'T CREATE -- I DO NOT CREATE A TABLE WITH THE LIST OF ALL THE VALUES, OKAY? SO IN OTHER WORDS, WHAT WE'RE SEEING IS THAT IF I CREATE HERE, IMAGINE YOU WANT TO CREATE SOMETHING LIKE A PERCENTAGE OF SALES, OKAY? A PERCENTAGE OF SALES WOULD BE THE DIVISION BETWEEN THE SALES AMOUNT AND A MEASURE THAT I STILL HAVE TO CREATE THAT I WILL CALL ALL SALES. SO IN ORDER TO MAKE THIS PERCENTAGE WORKING, I NEED TO CREATE A NEW MEASURE THAT I WILL CALL ALL SALES, AND ALL SALES IS EQUAL TO, FOR EXAMPLE, CALCULATE OF SALES AMOUNT, COMMA, AND I WANT TO REMOVE THE FILTER FROM THE SALES TABLE. WHAT HAPPENS IF I WRITE THIS? IF I MOVE THIS HERE, YOU SEE THAT FOR EVERY ROW, AND I CAN ALSO SHOW THE PERCENTAGE AT THIS POINT, SO YOU SEE THAT FOR -- OOPS. MADE A MISTAKE. I DIDN'T WANT TO DO THIS. I WANTED TO APPLY -- SO YOU SEE THAT THIS NUMBER, THIS PERCENTAGE IS OBTAINED BY DIVIDING THIS NUMBER BY THIS NUMBER, SO THE KEY IS HOW DO I OBTAIN THIS NUMBER USING THE CALCULATE THAT YOU SEE HERE. SO LET ME DESCRIBE ONCE AGAIN WHAT HAPPENS IN THE FILTER CONTEXT FOR THIS CELL. WE HAVE ONE BRAND, ONE COLOR IN THE FILTER CONTEXT. SO THE INITIAL FILTER CONTEXT IS ALWAYS THE SAME. FILTER CONTEXT, ONE BRAND, ONE COLOR. TWO FILTERS, EASY. ONE VALUE, ONE VALUE HERE. MY CALCULATOR SAYS ALL SALES. YOU SAY, MARCO, YOU WRITE A BIG TABLE BECAUSE ALL SALES SEEMS TO BE A TABLE THAT HAS ALL THE COLORS OF SALES. NO. WHAT I'M DOING IS NOT THIS. SO LET ME REMOVE THIS. WHAT I'M DOING IS THAT ALL SAYS REMOVE THE FILTER FROM EVERY COLUMN THAT BELONG TO THE SALES EXPANDED TABLE. SO ALL THE COLORS OF SALES, ALL THE COLORS OF PRODUCT, ALL THE COLUMNS OF DATE, ALL THE COLUMNS OF CUSTOMER, BECAUSE SALES HAS CONNECTION TO ALL THE OTHER TABLES, SO THE EXPANDED TABLE HAS EVERYTHING, USUALLY BECAUSE TSS A STAR SCHEMA. WHEN YOU REMOVE A FILTER, IT'S A VERY EFFICIENT OPERATION BECAUSE WE DON'T ACTUALLY -- NEW INDEXES. WE JUST REMOVE EXISTING FILTERS, SO IT IS USUALLY A NON-SUPPORTIVE OPERATION IN OUR CALCULATION. OR EXCEPT REMOVES ALL THE FILTERS FROM THE COLUMN OF A TABLE BUT ONE OR TWO COLUMNS. SO ALL EXCEPT SAYS REMOVE FROM EVERYTHING BUT KEEP -- IF THERE IS A FILTER IN COUNTER-REGION, YOU CAN KEEP THE FILTER THERE. YOU CAN WRITE A SYNTAX THIS WAY, WHICH IS NOT THE SAME AS SAYING ALL AND THEN VALUES. THINK ABOUT THIS. ALL CUSTOMER VALUES . . . THAT'S A SYNTAX IN DAX. IT'S A TABLE. AND IF I WRITE BOTH CONDITIONS, ALL REMOVES FILTER AND -- APPLY FILTERS. WHAT I'M SAYING IS NOT THE SAME. WELL IF YOU HAD A FILTER -- IT IS THE SAME. BUT IMAGINE IF YOU HAD A FILTER OVER CONTINENT, CONTINENT IS THE LIST OF THE COUNTRIES OF THAT CONT TENT, BUT IF I WRITE ALL EXCEPT CUSTOMER COUNTRY REGION, I REMOVE THE FILTER FROM ALL THE COLUMNS, I KEEP THE FILTER IN COUNTRY REGION IF THERE IS A FILTER ON COUNTRY REGION. GUESS WHAT, I DON'T HAVE A FILTER OVER COUNTRY REGION. I HAVE A FILTER OVER CONTINENT. SO THE RESULT IS I SEE ALL THE CITIES, ALL THE COUNTRIES. BUT IF I HAVE A FILTER OVER CONTINENT AND I EXECUTE ALL IN VALUES, I REMOVE THE FILTER FROM THE CUSTOMER, AND I RESTORE THE FILTER FOR COUNTRY OR REGION COLUMN THAT IS VISIBLE, AND I GET THE LIST OF ALL THE COUNTRIES IN THE CURRENT CONTINENT. OR IN THE CURRENT CITY OF THE CUSTOMER. OR IN THE CURRENT AREA. I DON'T HAVE TO KNOW WHERE THE FILTER WAS. I JUST WANT TO GET -- I WANT TO PRESERVE THE COUNTERS THAT ARE VISIBLE IN ANOTHER WAY IN THE FILTER CONTEXT, AND THIS COULD BE USEFUL IN CERTAIN CALCULATIONS. NOW THESE CAN BE CONTROLLED THIS WAY AND CALCULATE CAN ENABLE YOU TO CONTROL THE FILTER CONTEXT, BUT CALCULATE CAN ALSO APPLY AN OPERATION THAT INVOLVES THE ROW CONTEXT. AND THE ROW CONTEXT IS NOT A FILTER. THE ROW CONTEXT IT RATES, AND IN THIS CASE, SUM X CUSTOMER SAY SOME AMOUNT, WE PRODUCE A STRANGE NUMBER BECAUSE WE'RE NOT SAYING WE WANT TO SUM THE AMOUNT OF THE SALES FOR EACH CUSTOMER. WE'RE SAYING THE SUM SALES AMOUNT FOR THE ENTIRE TABLE. WHY THE ROW CONTEXT DOES NOT GET THE CURRENT CUSTOMER? BECAUSE WE SAID ONLY THE FILTER CONTEXT PROPAGATES IT THROUGH THE RELATIONSHIP. THE ROW CONTEXT IGNORES THE RELATIONSHIPS. AND WHEN YOU HAVE THE SYNTAX, ITERATES CUSTOMERS, SOME SALES AMOUNT AGGREGATES THE AMOUNT IN THE TABLE. THERE IS NOTHING SAYING I ONLY WANT TO FILTER THE SALES FOR THE CUSTOMER. WE CAN OBTAIN THIS BEHAVIOUR BY WRITING CALCULATE. IF YOU WRITE A CODE THIS WAY, YOU ARE SAYING FOR EACH CUSTOMER EXECUTE THIS CALCULATE. CALCULATE TRANSFORMS THE CURRENT ROW CONTEXT INTO AN EQUIVALENT FILTER CONTEXT. AT THIS POINT, THE AMOUNT IS AN AGGREGATION THAT HAPPENS WITHIN A FILTER CONTEXT THAT IS RESTRICTED BY JUST ONE CUSTOMER. AND THIS CODE WORKS AS WE MIGHT EXPECT, RIGHT? SO USUALLY THE CONTEXT TRNGS WORKS THIS WAY, AND THE CONTEXT TRANSITION IS IMPLICIT WHENEVER YOU WRITE A MEASURE REFERENCE. SO LET ME GO BACK TO THE EXAMPLE SO THAT I CAN SHOW YOU A DEMO OF THIS. LET'S SAY THAT WE HAVE HERE -- FOR EXAMPLE, HERE WE HAVE -- LET ME REMOVE THE AMOUNT AND LET'S INCLUDE ONLY THE SALES AMOUNT. WE CAN DO THE EXAMPLE USING -- WHY IS THIS SORTED THIS WAY? OH, THIS IS BAD. OKAY. LET ME FIX THIS. WHY DID WE HAVE THE DATE TABLE THAT IS NOT SORTED CORRECTLY? SO WE JUST HAVE TO FIX THIS, SORTING BY COLUMN MONTH NUMBER. MUCH BETTER. SO I HAVE THIS VISUALIZATION WHERE I CAN SAY, OKAY, FOR EVERY MONTH I SEE A NUMBER. WHAT HAPPENS IF I CLICK HERE, A MEASURE THAT SAYS I WANT TO SEE THE AVERAGE BY CUSTOMER FOR EACH MONTH OR FOR EACH -- I CAN WRITE AVERAGE, SO AVERAGE CUSTOMER IS EQUAL TO AVERAGE X FOR EACH CUSTOMER I WANT TO RETRIEVE THE SUM OR SUM X OF SALES QUANTITY -- THIS IS A MISTAKE, OKAY? I'M REPEATING THE SAME MISTAKE THAT WE HAVE SEEN IN THE SLIDES. BECAUSE I'M NOT TRANSFORMING THE CURRENT ROW CONTEXT INTO FILTER CONTEXT. SO THIS IS A MEASURE THAT PRODUCES A WRONG NUMBER. IF YOU LOOK AT THE NUMBER, HOW IS IT POSSIBLE THAT THE AVERAGE BY CUSTOMER IS IDENTICAL TO SALES AMOUNT? YOU SEE THE PROBLEM, RIGHT? BECAUSE FOR EVERY CUSTOMER I'M COMPUTING THE SAME NUMBER AND THE AVERAGE BY CUSTOMER IS THE SAME NUMBER. WHAT I WANT TO DO INTERNALLY TO THAT CALCULATION, I WANT TO RETRIEVE ONLY THE SALES FOR THE CURRENT CUSTOMER. FOR THOSE OF YOU THAT KNOW DAX, THERE IS AN ALTERNATIVE THAT IS THIS. RELATED TABLE GETS THE -- GIVE ME THE ROWS IN SALES FOLLOWING THE ONE-TO-MANY RELATIONSHIP FROM THE CURRENT CUSTOMER. BUT A BETTER WAY TO DO THIS IS USING CALCULATE. IF I USE CALCULATE, I'M SAYING TRANSFORM THE CURRENT ROW CONTEXT IN THE CUSTOMER TABLE INTO AN EQUIVALENT FILTER CONTEXT. AND AT THIS POINT, THE ENTIRE CALCULATION SUM X BLAH, BLAH, BLAH IS EXECUTED FOR A SINGLE CUSTOMER IN THE FILTER CONTEXT AND THIS PRODUCES A RESULT THAT MAKES MORE SENSE TO THE AVERAGE CUSTOMER MONTH BY MONTH IS A SMALLER NUMBER THAN THE SALES AMOUNT. NOW WHEN YOU HAVE THIS, IF YOU REMEMBER, THIS EXPRESSION WAS SALES AMOUNT, RIGHT? I CAN WRITE THIS. AND IF I WRITE THIS, OF COURSE I OBTAIN THE SAME RESULT. HOWEVER, WHAT -- IT COULD BE UNEXPECTED IF IT IS -- THAT IF NOW I REMOVE CALCULATOR, THE RESULT IS STILL THE SAME EXECUTING THE CONTEXT TRANSITION. STILL THE SAME VALUE. WHY? BECAUSE WHENEVER YOU HAVE A MEASURE REFERENCE IN A ROW CONTEXT, THERE IS ALWAYS AN IMPLICIT CALCULATE OUTSIDE. THERE IS ALSO A CONTEXT TRANSITION. SO A MEASURE REFERENCE, AND THIS IS THE REASON WHY WE WANT TO BE VERY EXPLICIT WHEN WE HAVE A MEASURE REFERENCE. WE WANT TO IDENTIFY IT WITHOUT SAYING -- WHEN YOU HAVE A ROW CONTEXT, IT COULD BE EXPENSIVE. THIS IS NOT FOR FREE. IF I REALLY WANT TO DO THIS CALCULATION, INSTEAD OF COMPUTING THE SALES AMOUNT CUSTOMER BY CUSTOMER, IT WOULD HAVE BEEN SMARTER TO GET THE SYSTEM OUT FOR THE CUSTOMER, DIVIDING IT BY THE NUMBER OF CUSTOMERS. BECAUSE THIS WAY I AVOID TO DO MANY TRANSACTION. NOW YOU HAD TO PAY ATTENTION TO ONE VERY COMMON MISTAKE. WHAT HAPPENS IF I CALL A MEASURE REFERENCE ITERATING A TABLE THAT DOESN'T HAVE A PRIMARY KEY? I COULD MAKE A MISTAKE. IF YOU -- SAFE STABLE, IT IS POTENTIALLY DANGEROUS BECAUSE THE SAFE STABLE COULD HAVE DUPLICATED ROWS. YOU HAVE A PRODUCT DATE CUSTOMER. THE SAME CUSTOMER BUYS THE SAME PRODUCT THE SAME DAY. O, I ENTER INTO THE STORE. I BUY A BOTTLE OF WATER. THEN I GO OUT, BUT TWO HOURS LATER I WANT OTHER WATER, I COME BACK, I GET THE SAME PRODUCT, THE SAME DAY, THE SAME CUSTOMER, THE SAME PRICE, EVERYTHING. AND THERE ARE TWO DIFFERENT TRANSACTIONS THAT WE WANT TO COMPUTE. WHAT HAPPENS AT THAT POINT? WELL, BECAUSE THE CONTEXT TRNGS TRANSFORMS IT, IT MEANS THAT ALL THE COLUMNS BECOMES FILTERS. A CONTEXT TRANSITION TRANSLATES EVERY COLOR INTO FILTER. AND WHAT HAPPENS IF YOU HAVE TWO ROWS THAT HAVE THE SAME VALUE? IN THIS CASE WE HAVE THREE DIFFERENT ROWS WITH THREE DIFFERENT VALUES. THAT'S FINE. BUT IN CASE WE HAVE THE SAME VALUE IN TWO ROWS, THEN THINK ABOUT THIS, THE SECOND ROW GENERATES A FILTER CONTEXT THAT INCLUDES TWO ROWS. BUT THEN THE ITERATION OVER SALES CONTINUES, AND FOR THE THOROUGH, IT WILL GENERATE THE SAME FILTER CONTEXT COMPUTING THE SAME ROWS MULTIPLE TIMES, SO WE ARE INFLATING THE RESULT. THIS COULD GENERATE ERRORS. WRITING A MEASURE IN AN ITERATOR THAT HAS A LOT OF ROWS COULD BE EXPENSIVE FOR THE PERFORMANCE, BUT DOING THIS IN A TABLE THAT MIGHT HAVE DUPLICATED ROWS COULD BE ALSO DANGEROUS FOR THE RESULT THAT IS NOT CORRECT. SO YOU HAVE TO PAY ATTENTION TO THIS, AND YOU HAVE TO THINK THAT THE CONTEXT -- COULD BE DANGEROUS IN THESE CASES -- OVER A TABLE THAT HAS A PRIMARY KEY, ONE TOO MANY RELATIONSHIP, CUSTOMER PRODUCT, NOT A PROBLEM. SALES, TRANSACTIONS, YOUR FACT TABLE IN THE STAR SCHEMA, BE CAREFUL. POSSIBLY DON'T DO THAT. TRY TO SEE IF THERE IS ANOTHER WAY INSTEAD OF ITERATING THAT TABLE WITH A MEASURE. ITERATING THE TABLE USING ONLY COMMON REFERENCES, THIS IS FINE. NOW ONE LAST DETAIL THAT I WANT TO COVER TODAY IS ABOUT THE CALCULATE EXECUTION ORDER, WHICH IS, MARCO, THESE WE HAVE ALL. WHAT IS THE ORDER? WELL, CALCULATOR, AT THE BEGINNING EVALUATES THE FILTERS BEFORE ANYTHING ELSE. BUT THE RESULT OF THE FILTERS ARE TABLES THAT WILL BE USED LATER. SO FIRST IT EVALUATES THE FILTERS. THEN IT EXECUTES THE CONTEXT TRANSITION. THEN IT APPLIES THE FILTER MODIFIERS, WHICH MEANS ALL CAN REMOVE THE CONTEXT TRANSITION, BUT THE FILTER IS REMOVED BEFORE THE TRANSITION, AND FINALLY IT APPLIES THE FILTER TO THE FILTER CONTEXT. IF YOU GO TO DAX GUIDE, IF YOU WANT TO GET THE LEAST OF THESE RULES IN MORE DETAIL, I CAN SHOW YOU. WE HAVE IN THE DOCUMENTATION OF DAX GUIDE, WE INCLUDED THIS LIST OF DETAIL RULES, SO IF YOU HAVE ANY DOUBT, IF YOU HAVE A DOUBT ABOUT WHAT HAPPENS EXACTLY IN THIS CASE, LOOK AT THIS DOCUMENTATION, WHICH IS AN EXTRACT OF THE SECOND EDITION OF THE GUIDE TO DAX BOOK THAT WILL BE AVAILABLE IN A COUPLE OF MONTHS. SO BASICALLY I ALREADY SHOWN YOU, THIS IS JUST A RECAP OF WHAT WE HAVE SEEN. WE CAN APPLY THESE TECHNIQUES TO OBTAIN DIFFERENT CALCULATIONS. I TRIED TO SHOW YOU MAYBE SOME EXAMPLE THAT WAS DIFFERENT FROM THE ONE LISTED HERE, BUT THE IDEA IS THAT WE HAD TO THINK ABOUT THE EVALUATION CONTEXT AS A SUM OF TWO ELEMENTS. THE FILTER CONTEXT AND THE ROW CONTEXT. THEY ARE DIFFERENT. THEY ARE NOT THE SAME. THE ROW CONTEXT IT RATES. THE FILTER CONTEXT FILTERS. IT'S APPLIED TO AGGREGATIONS, TO TABLE EXPRESSIONS. THE ROW CONTEXT IS OPERATED ONLY FOR FUNCTIONS THAT YOU CAN RETRIEVE IN DAX GUIDE. AND OF COURSE FOR THE CALCULATED COLUMN. CALCULATOR MANIPULATES THE FILTER CONTEXT, BUT ALSO TRANSFORMS A ROW CONTEXT INTO AN EKWAIFLT FILTER CONTEXT WITH A TECHNIQUE THAT -- FILTER FOR EVERY COLUMN OF THE TABLE THAT YOU ARE ITERATING. AND IF YOU ARE ITERATING A TABLE THAT HAS MANY COLUMNS, IT COULD BE AN EXPENSIVE FILTER. AND IF YOU -- ITERATING A TABLE THAT DOESN'T HAVE A PRIMARY KEY, PAY ATTENTION TO THE POSSIBLE DUPLICATIVE ROLES THAT YOU CAN HAVE. FINALLY REMEMBER THAT WHEN YOU SPECIFY A FILTER IN A CALCULATE, YOU ARE SPECIFYING A FILTER OF THE EXPANDED TABLE WHICH POTENTIALLY AFFECTS MANY OTHER COLUMNS. AS LONG AS YOU REMOVE A FILTER WITH ALL, THAT'S FINE. BUT IF YOU WRITE A FILTER WITH A FILTER, IT COULD BE DANGEROUS AND EXPENSIVE. SO PAY TOENGS TO THE DETAILS IF YOU WANT TO CONTROL THE RESULT AND THE PERFORMANCE OF DAX. REMEMBER TO EVALUATE THE SESSION. THANK YOU VERY MUCH. I PROBABLY HAVE TO RUN FOR THE NEXT SESSION, BUT I'M AVAILABLE FOR YOU IF YOU HAVE ANY QUESTIONS. THANK YOU VERY MUCH.
