相册

DAX Leaning Jurnal – Chapter 2

Dumbo’s DAX learing plan

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

第一部分学习打卡 7.19 – 7.22

第二章

理解DAX计算

今天第一次使用天行老师推荐的幕布做笔记,一键转成思维导图的感觉实在太好了。 重读概念部分,发现自己之前忽略的很多细节。温故而知新呀。

Chapter 2 Introducing DAX

  • Chapter 2 – 理解DAX计算
    • 如何引用某个表格中的一列
      • ‘表格名'[列名]
        • 当表格名不是以数字开头,或名称中不包含空格,或不是系统名词比如:( Date, Sum, Calculate)时,可以省略单引号。
        • 如果在表格内定义一个DAX公式时,也可以省略表格名,直接使用[列名]来引用这一列。例如,在Sales中,写一个有关[Quantity]的度量值或计算列,可直接写[Quantity],省略’Sales’。但是并不建议这么做。
        • 度量值和计算列的区别: 引用度量值时,无论在哪里都可以忽略前面的表名。而计算列只有在表格内引用时,才可以忽略。
          • Sales[Quantity] * 2 — This is a column reference
          • [Sales Amount] * 2 — This is a measure reference  
    • DAX中的注解
      • 单行注解 –  –  或 //  
      • 多行注解 starts with /* and ends with */.  
      • 避免在DAX公式的末尾写注解,因为一开始并看不到,而且不被daxformatter这类工具所支持
    • DAX 数据类型
      • 整型 interger : 64-bit value  
      • 浮点型 decimal : as a double-precision floating-point value  
      • 货币 currency : Fixed Decimal Number in Power BI, stores a fixed decimalnumber.  It can represent four decimal points and is internally stored as a 64-bit integer value dividedby 10,000.  对货币类型值进行计算时,只代入四位小数进行计算 ,需要考虑到精确度的问题。
      • 日期 datetime : This format uses a floating-point number internally, whereinthe integer corresponds to the number of days since December 30, 1899, and the decimal part identifies the fraction of the day. Hours, minutes, and seconds are converted to decimal fractions of a day.  
      • 布尔型 Boolean : logical conditions. 
      • 字符串 String : 16 BIT unicode string/ Not case sentitive
      • 变量 Variant : 当返回值不确定类型时,就是一个变量型,比如 IF ( [measure] > 0, 1, “N/A” )  结果可能是整型1 也有可能是字符串 N/A。且变量型不能用作定义某个列的数据类型。
      • 二进制型 Binary : 用来存储图片等无结构数据类型。
      • DAX在计算时可以根据给出的数据类型,自动定义所要结果的数据类型。比如,如果是一个整型乘以一个数字,那么结果也是整型 (operator overloading )
      • DAX可以自动将字符串转换为数字,数字转为字符串。例如:= 5 & 4  的结果”54″就是字符串 而 = “5” + “4”  的结果9是一个整型数字。
      • The resulting value depends on the operator and not on the source columns, which areconverted following the requirements of the operator.  计算符号会改变计算结果的数据类型
    • DAX 计算操作符
      • 括号 ( )
      • 数学计算 + – * / 加减乘除
      • 比较符 = <> > >= < <=
      • 字符链接 Text concatenation &
      • 逻辑符号 && || IN NOT
    • 表格建造器
      • { ( ), ( ) }
      • ( 一行 )
      • we can rewrite it using the IN operator with a table constructor that has a single row  
    • 条件声明
      • IF Function; 三个参数,但只有前两个是强制的,第三个是可选,默认值是空白。
  • Chapter 2 – 理解计算列和度量值
    • 计算列
      • A new column added to your model
      • Created by a DAX formula
      • Like any other column in a table : used to create relationship
      • Import mode : all calculated columns occupy space in memory/uses precious RAM
      • In DirectQuery mode, calculated columns are computed on the fly when the Tabular engine queries the data source.
      • Computing the duration of an order : convert the result to an integer : Sales[DaysToDeliver] = INT ( Sales[Delivery Date] – Sales[Order Date] )
    • 度量值
      • 如果不想对每一行进行计算,而是对每一行计算一个求和结果时
    • 度量值与计算列的区别:背景不同
      • A measure is evaluated in the context of a visual element or in the context of a DAX query.
      • a calculated column is computed at the row level of the table it belongs to.(逐行进行求值)
      • A measure needs to be defined in a table,but it does not really belong to the table.
    • 两者如何进行选择?
      • 一定要使用度量值的情况:the values need to be presented as aggregates in a report
        • To calculate the profit percentage of a report selection
        • To calculate ratios of a product compared to all products but keep the filter both by year and by region
      • 一定要选择计算列的情况:
        • 将计算结果用于切片器,或者结果是matrix 或 powerpivot中的一列,或者结果用于dax中的筛选条件 as a filter condition
        • Define an expression that is strictly bound to the current row. 计算当前行
        • Categorize text or numbers
    • Using measures in calculated columns: context transition
  • Chapter 2 – 变量
    • 使用关键字 VAR定义,关键字RETURN用于返回计算结果
    • dax重的变量都是局部变量,在当前计算式中有效,dax中不存在全局变量
    • 在一个dax计算式中,如果一个变量定义了但没有使用,那就不会对其进行计算
    • 使用变量来避免dax计算式的重复
  • Chapter 2 – 处理DAX中的错误
    • Conversion error 数据转换错误
      • 多出现在对不同类型的数据进行计算时,如果dax没有根据计算符自动转换格式
      • 解决方法:
        • add error detection logic in DAX expressions to intercept error conditions and return a result that makes sense. 在dax表达式里加入错误检测逻辑
        • 具体例子?
    • Arithmetic operations errors 算术运算错误
      • 不符合运算规则的计算:
        • 被除数为0 => infinity;除数为零=>NaN; NaN/NaN = NaN
        • Square root of a negative number:
      • Use the ISERROR function
      • Special values like NaN are displayed in the user interface of several tools such as Power BI as regular values.
    • Empty or missing values 空缺或缺失值
      • Combined with other elements in a calculation 当与计算中的其他元素结合使用时,空值可能会导致意外结果或计算错误。
      • Using the value BLANK 返回一个空值
        • 含有blank的表达式可能返回值,也可能返回空白,取决于所需的计算。
        • It is impossible to distinguish whether an expression is 0 (or empty string) or BLANK using an equal operator.
        • ISBLANK function to check whether a value is BLANK or not
        • 在不同情景下,blank可以是blank或者0
    • Intercepting errors
      • IFERROR(… ; …. ; …) function = IF ( ISERROR( …); …; …)
      • Testing parameters before using them:
      • ISBLANK detects empty values, returning TRUE if its argument is BLANK.
      • Variables : errors must be checked at the time of variable definitionrather than where we use them.
      • Avoid using error-handling functions : IFERROR ISERROR : performance issues, invoke another error
    • Generating errors : in a more meanningful way
  • Chapter 2 – Formatting DAX code
    • “format your code and never write everything on a single line; otherwise, you will be in trouble sooner than you expect.”
    • Using variables can help you read the code.
    • http://www.daxformatter.com/
  • Chapter 2 – 聚合器和迭代器 Aggregators and iterators
    • 聚合器 : aggregate the values of a column in a table and return a single value.  (SUM, AVERAGE, MIN, MAX) DAX never considersempty cells when it performs the aggregation. 
    • 迭代器 : aggregate an expression instead of a single column.  an expression that is evaluated for each row of the table.  (SUMX, AVERAGES, MINX, MAXX)
      • The seconde one does not rely on the presence of a calculatedcolumn, thus, we can build the entire report without creating expensive calculated columns.  
    • The basic aggregation functions are a shortened version of the corresponding X-suffixedfunction. 
  • Chapter 2 – Using common DAX functions
    • Aggregation functions  
      • AVERAGEA, COUNTA, MINA, and MAXA  
      • COUNT operates on any data type, apart from Boolean.
      • COUNTA operates on any type of column.
      • COUNTBLANK returns the number of empty cells (blanks or empty strings) in a column.
      • COUNTROWS returns the number of rows in a table.
      • DISTINCTCOUNT returns the number of distinct values of a column, blank value included ifpresent.
      • DISTINCTCOUNTNOBLANK returns the number of distinct values of a column, no blank valueincluded.  
    • Logical functions
      • AND, FALSE, IF, IFERROR, NOT, TRUE and OR
      • SWITCH => Multiple IF
    • Information functions  : Return a Boolean value and can be used in any logical expression. 
      • ISBLANK, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, ISTEXT
        • Test whether a text value is convertible to a number  :
          • Sales[IsPriceCorrect] = NOT ISERROR ( VALUE ( Sales[Price] ) )  
            • For example,the conversion fails if some of the rows have an “N/A” string value for price  
          • Sales[IsPriceCorrect] = ISNUMBER ( Sales[Price] )  
    • Mathematical functions  
      • ABS EXP FACT LN LOG LOG10 MOD PI POWER QUOTIENT SIGN SQRT
      • RAND RANDBETWEEN
      • EVEN ODD
      • GCD LCM
      • FLOOR TRUNC ROUNDDOWN : Specify the number of digits to round. 
    • Trigonometric functions  
      • Useful for certain calculations: COS, COSH,COT, COTH, SIN, SINH, TAN, and TANH. 
      • Prefixing them with A computes the arc version (arcsine,arccosine, and so on). 
      • DEGREES Conversion to degrees
      • RADIANS conversion to radians
      • SQRTPI computes the square root of its parameter after multiplying it by PI
    • Text functions 
    • Conversion functions
      •  CURRENCY INT DATE TIME VALUE FORMAT DATAVALUE
        • CONCATENATE
        • CONCATENATEX
        • EXACT
        • FIND
        • FIXED
        • FORMAT
        • LEFT
        • LEN
        • LOWER
        • MID
        • REPLACE
        • REPT
        • RIGHT
        • SEARCH
        • SUBSTITUTE
        • TRIM
        • UPPER
        • VALUE
    • Date and time functions
      • DATE, DATAVALUE, DAY, EDATE, EOMONTH, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, WEEKNUM, YEAR and YEARFRAC
      • Using date and time functions to extract date information
    • Relational functions
      • RELATED RELATEDTABLE