Power BI

From Wiki
Jump to: navigation, search


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.

Query Editor

Custom Column Formulas in M

IF Statements
=if [Value] > 0 then [Result1] else [Result2]

=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


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