Power BI
Contents
Guides
MS Power BI Overview Contains a full pdf guide
Overview
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.
Query Editor
Custom Column Formulas in M
IF Statements
=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
Measures
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 =SUMX(FILTER(InternetSales, InternetSales[NumberOfSales]>5),[TXSales])
MS SUMX Function (DAX)
MS FILTER Function (DAX)
FORMAT Function
Numeric Formats
The following table identifies the predefined numeric format names.
Example uses = FORMAT( 12345.67, "style")
Format specification | Example | Description |
"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. |
Random Formats
Formula | 1,000,000 |
FORMAT(1000000, "#,##0,k") | 1,000k |
FORMAT(1000000, "#,##0,,M”) | 1M |
FORMAT("0,,.##M") | 1.00M |
More Examples: https://msdn.microsoft.com/en-us/library/ee634206.aspx