# DAX LEANING JURNAL – CHAPTER 3

Dumbo’s DAX learing plan

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