七月 26, 2019

DAX LEANING JURNAL – CHAPTER 3

作者 ladydumbo

Dumbo’s DAX learing plan

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

第二部分学习打卡 7.23– 7.25

Chapter 3 Using Basic Table Functions : functions return a table

  • An expression that results in a single value is called a scalar expression. 
  • How to use the result of a table expression?
    • Not as the value of a measure or of a calculated column ( both require a scalar value)
    • Assign the result to a calculated table.
  • Calculated table
    • a table whose value is determined by a DAX expression rather than loaded form a data source
  • EVALUATE
    • DAX expression that returns a table + EVALUATE => DAX Query
    • DEFINE MEASURE  : Define measures local to the query
    • ORDER BY : sort order of tables
  • FILTER
    • return all the rows satisfying the condition
    • table function + iterator
    • used to reduce the number of rows in iterations
      • 计算红色产品的销量:
    • Nest FILTER
      • = combian the conditions of two FILTER functions with AND
      • apply the most selective condition first, in order to reduce the number of iterations needed to be checked
  • ALL & ALLEXPECT
    • ALL
      • returns all rows of a table or all the values of one or more columns
      • useful in computing percentages or ratios
      • parameter cannot be a table expression, need to be either a table name or a list of column names.
      • ALL on a column?
        • ALL returns all the distinct values of the column in the entire table.
        • multiple columns from the same table => all the existing combinations of values in those columns. 
    • ALLEXPECT
      • mainly useful as a parameter of CALCULATE in advanced calculations
    • Top categories and subcategories  (经典案例)
  • VALUES,DISTINCT and the blank row
    • difference between VALUES / DISTINCT : how they handle the blank row
    • ALL/VALUE
      • ALL always returns all the distinct values of a column. ( ALLNOBLANKROW )
      • VALUES returns only the distinct visible values.
    • where comes the blank rows?
      • one-side of a relationship, all the ophaned rows in many side
    • Values considers the blank row is a valid row, and it returns it.
    • a well-designed model should not present any invalid relationships.
    • VALUES and DISTINCT also accept a table as an argument. In that case, they exhibit different behaviors:
      • ■ DISTINCT returns the distinct values of the table, not considering the blank row. Thus, duplicated rows are removed from the result.
      • ■ VALUES returns all the rows of the table, without removing duplicates, plus the additional blank row if present. Duplicated rows, in this case, are kept untouched.
  • Using tables as acalar values
    • Table function VALUES used to compute scalar values : a table with a single row and a single column
    • VALUES => HASONEVALUE : checks whether a column only has one visible value  
    • SELECTEDVALUE
  • ALLSELECTED
    • Running percentage calculation

第三章主要是对于表格函数的学习,是最终结果返回一张计算表作为结果的函数。