七月 26, 2019
DAX LEANING JURNAL – CHAPTER 3
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
- DAX expression that returns a table + EVALUATE => DAX Query
- DEFINE MEASURE : Define measures local to the query
- ORDER BY : sort order of tables
- 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
- 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.
- 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 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
- Running percentage calculation