七月 29, 2019

DAX LEANING JURNAL – CHAPTER 4

作者 ladydumbo

Dumbo’s DAX learing plan

从2019年7月19日开始,读 <The definitive guide to DAX> edition 2这本书,坚持100天每日做笔记。

第三部分学习打卡 7.25– 7.28

第四章主要讲解Evaluation contexts,懂得如何使用EARLIER很重要。

Chapter 4 Understanding evaluation contexts

  • Introducing
    • An evaluation context : context under which a DAX expression is evaluated
    • Filter contexts
      • A context that filters tables
      • The filter context of a cell is computed by merging together all the filters coming from rows,columns,slicers, and any other visual used for filtering => A filter context is a set of filters
    • Row context
      • Not a tool to filter tables, it’s used to iterate over tables and evaluate column values
      • The row context references a row in the result of a DAX table expression
      • row context exists when we create a calculated column or when computing an expression inside an iteration ( X-function )
      • Measure / Calculated column => automatic row context
  • Using the row context with iterators
    • !! Any piece of DAX code works in the context where it is called.
    • All iterators behave the same way:
      • Evaluate the first parameter in the existing contexts todetermine the rows to scan
      • Create a new row contextfor each row of the table evaluated in the previous step
      • Iterate the table and evaluate the second parameter in the existing evaluation context, including the newly created row context
      • Aggregate the values computed during the previous step
  • Working with several tables
    • Row contexts and relationships
      • They do not interact in any way, at least not automatically. 
      • RELATED 以及RELATEDTABLE的使用
        • 从 * 表上,获取 1 表上的数据,From * to 1,用RELATED. If you want to access columns on the one side of a relationship from the table on the many side of the relationship,  you must use the RELATED function. 
        • From 1 to *,用RELATEDTABLE . You can use RELATEDTABLE onthe one side of the relationship and it returns all therows (of the table on the many side) that are related with the current one.
        • 对于1:1的关系,两个函数可互换
        • Limite:
          • Therelationship type must be the same, and also the same direction.
          • ELATEDTABLEdoes not work if you try to traverse a many-tomany relationship.
    • Filter context and relationships 
      • The filter context propagates through a relationship if the filtering direction set on the relationship itself makes propagation feasible. 
      • The arrow is always enabled from the one side to the many side of any relationship. 
      • The propagation of the filter context in a DAX expression happens automatically, whereas propagation of row contexts does not and it is required to specify the propagation using RELATED and RELATEDTABLE 
  • FILTER, ALL and context interaction
    • FILTER :
      • Do not change the filter context
      • An iterator scans a table (already filtered by the filter context) and it returns a subset of that table, according to the filtering condition.
    • ALL
      • Returns the content of a table ignoring the filter context
      • FILTER + ALL : FILTER does not iterate Product. Instead, it iterates ALL ( Product )  
  • EARLIER
    • retrieves the value of a column by using the previous row context instead of the last one
  • Nested row contexts on the same table ( multiple row contexts )
    • understand the different row context of different DAX code
  • Nested row contexts on different tables
  • DISTINCT
    • 计算消费顾客的平均年龄
      • SUMMARIZE, can generate the existing unique combinqtions of two columns.