MS Power BI Overview Contains a full pdf guide
Power query formula language (informally known as "M")
M formulas are used when transforming your data in the query editor before the end table loads.
DAX (Data Analysis Expressions)
DAX is used when creating measurements outside the query editor.
DAX is a library of functions and operators used to build formulas and expressions in Power BI Desktop. Your typical formula used in excel can be slightly different in DAX.
Custom Column Formulas in M
=if [Value] > 0 then [Result1] else [Result2]
AND, OR, NOT
=if [Value]>0 AND [Value2]=0 then ...
IF Error (try and otherwise)
= try if [Value] = ... then ... else ... otherwise 0
Data cannot be divided by 0 or null.
if [Value] = null then 0 else ...
DAX & Visual Editor
Everything is in context of a table -
- A measure is used for aggregate functions
- Calculated "custom" columns calculate the data on a per row basis
|Name||Length||Width||Area (Custom Column)|
|Sue||2||4||= [Length] * [Width]|
|Frank||3||1||= [Length] * [Width]|
A measure is used to aggregate a column(s) = SUM(tablename.[Area])
Note: Measures are created in the Report, whereas Calculated Columns are created in the query editor and added to the table.
Tutorials on Creating Measures
SUMX & FILTER
When you create a measure
= SUM(InternetSales[TxSales]) , the report's filtering doesn't apply.
You need to filter the data in the formula
MS SUMX Function (DAX) MS FILTER Function (DAX)
The following table identifies the predefined numeric format names.
= FORMAT( 12345.67, "style")
|"General Number"||12345.67||Displays number with no thousand separators.|
|"Currency"||$12,345.67||Displays number with thousand separators|
|"Fixed"||12345.67||Displays at least one digit to the left and two digits to the right of the decimal separator.|
|"Standard"||12,345.67||Displays number with thousand separators, at least one digit to the left and two digits to the right of the decimal separator.|
|"Percent"||1,234,567.00 %||Displays number multiplied by 100 with a percent sign (%) appended immediately to the right; always displays two digits to the right of the decimal separator.|
|"Scientific"||1.23E+04||Uses standard scientific notation, providing two significant digits.|
More Examples: https://msdn.microsoft.com/en-us/library/ee634206.aspx