POWER BI

Measures

DAX formulas used to generate new calculated values

Like calculated columns - measure reference __________ tables or columns

entire

Unlike calculated columns - measures aren't ________ within tables

visible

Measure can be seen within a _____________________ like a chart of matrix

visualization

Use measure to create _________________, ________________ values that can be analyzed in the "values" field of a report visual

numerical, calculated

_________________ measures are created when you drag raw numerical fields into a report visual and manually select an aggregation mode (Sum, Average, Min, Max, Count, etc)

IMPLICIT

___________________ measures are created when you actually write a DAX formula and define a new measure that can be used within the model

EXPLICIT

Type of measure that can only be accessible within the specific visualization in which they were created - and cannot be referenced elsewhere

IMPLICIT

Type of measure that can be used anywhere in the report, and referenced by other DAX calculations to create "measure trees"

EXPLICIT

________ measures automatically create formulas based on pre-built templates or natural language prompts

QUICK

Quick measure ______________ can be used to build measures using predefined templates (weighted averages, percent difference, time intelligence, etc)

calculations

Quick measure ____________ can be used to find suggested measures based on natural language queries (ie "sum of qty sold by calendar year)

suggestions

Values are calculated based on information from each row of a table (row context)

calculated columns

Values are calculated based on information from any filters in the report (filter context)

measures

Appends static values to each row in a table and stores them in the model (which increases the file size)

calculated columns

Does not create new data in the tables themselves (doesn't increase file size)

measures

Recalculate on data source refresh or when changes are made to component columns

calculated columns

Recalculate in response to any change to filters within the report

measures

Primarily used for filtering data in reports (slicers/filters - use them as dimensions to change the way that you are viewing your data)

calculated columns

Primarily used for aggregating values in report visuals

measures

Calculated columns live in

tables

Measures live in

visuals

Create a dedicated table to store your measures

Option 1 - enter data into power query - Option 2 - create a calculated table using DAX directly in model i.e. Measure Table (DAX) = ("")

+ - * / ^

Arithmetic Operators

= > < >= <= <>

Comparison Operators

& && || IN

Text/Logical Operators

& (text/logical operator)

concatenates two values to produce one text string - i.e. [City] & " " &[State]

&& (text/logical operator)

create an AND condition between two logical expressions - i.e. ([State]="MA") && ([Quantity]>10)

|| (double pipe) (test/logical operator)

create an OR condition between two logical expressions - i.e. ([State]="MA") || ([State]="CT")

IN (text/logical operator)

creates a logical OR condition based on a given list (using curly brackets) - i.e. 'Store Lookup'[State] IN {"MA", "CT", "NY"}

Functions used for aggregation or iterative, row-level calculations

Math & Stats functions (common - SUM, AVERAGE, MAX/MIN, DIVIDE, COUNT/COUNTA, COUNTROWS, DISTINCTCOUNT) (iterator - SUMX, AVERAGEX, MAXX/MINX, RANKX, COUNTX)

Functions that use conditional expressions (IF/THEN statements)

Logical functions (IF, IFERROR, AND, OR, NOT, SWITCH, TRUE, FALSE)

Functions used to manipulate test strings or value formats

Text functions (CONCATENATE, COMBINEVALUES, FORMAT, LEFT/MID/RIGHT, UPPER/LOWER, LEN, SEARCH/FIND, REPLACE, SUBSTITUTE, TRIM)

Functions used to manipulate table and filter contexts

Filter functions (CALCULATE, FILTER, ALL, ALLEXCEPT, ALLSELECTED, KEEPFILTERS, REMOVEFILTERS, SELECTEDVALUE)

Functions that create or manipulate tables and output tables vs. scalar values

Table functions (SUMMARIZE, ADDCOLUMNS, GENERATESERIES, DISTINCT, VALUES, UNION, INTERSECT, TOPN)

Functions used to manipulate date & time values or handle time intelligence calculations

Date & Time functions (common - DATE, DATEDIFF, YEARFRAC, YEAR/MONTH, DAY/HOUR, TODAY/NOW, WEEKDAY, WEEKNUM, NETWORKDAYS) (time intelligence - DATESYTD, DATESMTD, DATESADD, DATESBETWEEN)

Functions used to manage & modify table relationships

Relationship functions (RELATED, RELATEDTABLE, CROSSFILTER, USERELATIONSHIP)

SUM

evaluates the sum of a column =SUM(ColumnName)

AVERAGE

returns the average of all the numbers in a column =AVERAGE(ColumnName)

MAX

returns the largest value in a column or between two scalar expressions =MAX(ColumnNameORScalar1, [Scalar2])

MIN

returns the smallest value in a column or between two scalar expressions =MIN(ColumnNameOrScalar1, [Scalar2])

DIVIDE

performs division and returns the alternate result (or blank) if DIV/0 =DIVIDE(Numerator, Denominator, [AlternateResult])

COUNT

counts the number of non-empty cells in a column (excluding Boolean values) =COUNT(ColumnName)

COUNTA

counts the number of non-empty cells in a column (including Boolean values) =COUNTA(ColumnName)

DISTINCTCOUNT

counts the number of distinct values in a column =DISTINCTCOUNT(ColumnName)

COUNTROWS

counts the number of rows in a specific table, or a table defined by a expression =COUNTROWS({Tabe{)

Checks if a given condition is met and returns one value if the condition is TRUE, and another if the condition is FALSE

IF, =IF(LogicalTest, ResultIfTrue, [ResultsIfFalse])

Evaluates an expression and returns a specified value if it returns an error, otherwise returns the expression itself (display custom value instead)

IFERROR, =IFERROR(Value, ValueIfError)

Evaluates an expression against a list of values and returns one of multiple possible expressions

SWITCH, =SWITCH(Expression, Value1, Result1, ..., [Else])

Checks whether both arguments are TRUE to return TRUE, otherwise returns FALSE - use && for more than two conditions

AND, =AND(Logical1, Logical2)

Checks whether any argument is TRUE to return TRUE, otherwise returns FALSE - use || to include more than two conditions

OR, =OR(Logical1, Logical2)

returns the number of characters in a string

LEN, =LEN(Text)

joins two (and only two) strings into one

=CONCATENATE(Text1, Text2) or use & operator as a shortcut, or to combine more than TWO strings

converts a string to upper or lower case

=UPPER/LOWER(Text)

returns a number of characters from the start/middle/end of a text string

=LEFT/RIGHT(Text, [NumChars])

=MID(Text, StartPosition, NumChars)

replaces an instance of existing text with new text in a string

=SUBSTITUTE(Text, OldText, NewText, [InstanceNumber])

returns the position where a specified string or character is found, reading left to right

=SEARCH(FindText, WithinText, [StartPosition], [NotFoundValue])

returns the current date or exact time

=TODAY/NOW()

returns the day of the month (1-31), month of the year (1-12), or year of a given date

=DAY/MONTH/YEAR(Date)

returns the hour (0-23), minute (0-59), or second (0-59) of a given datetime value

=HOUR/MINUTE/SECOND(Datetime)

returns a weekday number from 1 (Sunday) to 7 (Saturday), or the week # of the year

=WEEKDAY/WWEEKNUM(Date, [ReturnType])

returns the date of the last day of the month, +/- a specified number of months

=EOMONTH(StartDate, Months)

returns the difference between two dates, based on a given interval (day, hour, year, etc.)

=DATEDIFF(Date1, Date2, Interval)

returns related values in each row of a table based on relationships with other tables (need to be on the many side reaching over to the one side)

=RELATED(ColumnName) - the column from a related table containing the values you want to retrieve (works almost like a VLOOKUP in Excel)

filter modifier (like sum if and count if) - that allows you to overrule existing report filters and "force" new filter context

=CALCULATE(Expression, [Filter1], [Filter2]) expression = existing measure or DAX formula and filter are Boolean (T/F) expression or aggregation functions

returns all rows in a table, or all values in a column, ignoring any filters that have been applied (pro tip - instead of adding filter context, the ALL function removes it - this is often used in "% of Total" calculations, when the denominator needs to remain fixed regardless of filter context)

=ALL(Table or Column, [Column2], [Column3],...) = the table or column that you want to clear filters on

returns a table that represents a subset of another table or expression (it cuts out rows that do not meet the filter criteria) (iterator function that scans each row and eliminates rows - creates new table)

=FILTER(Table, FilterExpression)

allow you to loop through the same expression on each row of a table, then apply some sort of aggregation to the results (SUM, MAX, etc)

Iterator (or "X") functions

Still learning (29)

You've started learning these terms. Keep it up!

DAX

Data Analysis Expressions, a formula language used in Power BI for data modeling and analysis.