Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Power BI > Dax Functions > Flashcards

Dax Functions Flashcards

1
Q

RELATEDTABLE

A

Returns a table with all the rows related to the current one. One to many.
NumOfStudents = Countrows (Relatedtable (Students))
Can be used to generate a row context through a one to many relationship.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

“X” Aggregation functions

A 
Evaluate an expression for each row in a table.
Two parameters - table and expression.
SUMX
AVERAGEX
MINX
MAXX
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

IN

A

Check if the result of an expression is included in a list of values. Use curly brackets:

Student[State] IN {“NC”,”SC”,GA”}

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

SWITCH

A

Changes values, instead of nested IF().

SWITCH (
Product[Size],
"S", "Small",
"M", "Medium",
"L", "Large",
"XL", "Extra Large", 
"Other")
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

SWITCH(TRUE()…)

A

Can use for if then statements.

SWITCH (TRUE (),
Product[Size] = "S", 0.5,
AND ( Product[Size] = "L", Product[Price] < 100 ), 0.2,
Product[Size] = "L", 0.35,0
)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

MAX

A

Returns the largest value in a column
MAX ( Sales[SalesAmount] )
or between two columns
MAX ( Sales[Amount], Sales[ListPrice] )

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

IFERROR

A 
Returns an alternative expression 
IFERROR (
Sales[GrossMargin] / Sales[Amount],
BLANK () 
)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

DIVIDE

A

Avoid using IF to check for errors. Returns an alternative value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Variables

A

Calculate intermediate values in a measure or column.
VAR….
RETURN….

Variable values are assigned in the context they are defined, not in the context they are used. Calculate cannot modify a variable.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

FILTER

A 
FILTER
•Adds a new condition
•Restricts the number of rows of a table
•Returns a table
•Can be iterated by an «X» function
Needs a table as input
The input can be another FILTER

Filters on entire tables are bad. Narrow the table using ALL; add KEEPFILTERS if you need it to respond to other filters in the report.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

ALL

A 
ALL
•Returns all the rows of a table
•Ignores any filter
•Returns a table that can be iterated by an «X» function
Needs a table as input

Can be used with a single column
ALL ( Customers[CustomerName] )
•The result contains a table with one column that contains all unique values in the column

COUNTROWS ( 
ALL ( 
Orders[Channel], 
Orders[Color], Orders[Size]
))
Returns a table with all the values of all the columns passed as parameters.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

ALLEXCEPT

A

Removes all filters except those applied to the specified column.
Returns a table with all existing combinations of the given columns.
ALLEXCEPT(
Orders, Orders[City] )
{NEED MORE INFO HERE}

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

DISTINCT

A

Returns the unique values of a column, only the ones visible in the current filter context.

NumOfProducts :=
COUNTROWS (
DISTINCT ( Product[ProductCode] )
)
Compare to VALUES()
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

VALUES

A 
Returns the unique values of a column, only the ones visible in the current filter context, including the additional blank row if it is visible in the filter context.
NumOfProducts :=
COUNTROWS (
VALUES ( Product[ProductCode] )
)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

ALLNOBLANKROW

A

ALL returns the additional blank row, if it exists. ALLNOBLANKROW omits it.—- Returns only the existing products

COUNTROWS (
ALLNOBLANKROW ( Products[ProductKey] )
)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Counting different values

A

DISTINCT and ALLNOBLANKROW won’t include blank row, VALUES and ALL will include blank row

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

ALLSELECTED

A

With the date filters, I needed to always be evaluating the actual dates that were being filtered. I didn’t want to have any additional information. I only wanted to focus on what was selected. This is in simplest form what ALLSELECTED does. It focuses on what you have selected within your report page.
(Need more here)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

1 x 1 Tables

A

when a table contains one row and one column, can treat it as a scalar value. Use to display selected values as text, make visible to the user.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

SELECTEDVALUE

A 
Retrieves the value of a column when only one value is visible.
Sel Category := 
"You selected: " &amp; SELECTEDVALUE ( 
'Product Category'[Category],
"Multiple values")
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

ISEMPTY

A

Checks if a table is empty, Returns True or False.
ISEMPTY ( VALUES ( Product[Unit Price] ) )
is equivalent to
COUNTROWS ( VALUES ( Product[Unit Price] ) ) = 0

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Table variables

A

Variables can contain a scalar value or a table. Using table variables greatly helps in splitting complex expressions.

22
Q

Evaluation context

A

All of the filters etc. applied to a formula that affect its evaluation.
Slicers, visual filters, other graphs on page, etc.

23
Q

Filter context

A

Defined by
Row selection, column selection, report filters, slicer selection.
By default, flows from the one side to many side of relationships.

24
Q

Row context

A

Iterates. Needed to evaluate column values, it is the concept of “current row”. In calculated column, defined automatically for each row. In row iteration functions, it is defined by user formulas.
Does not propagate through relationships.

25
Q

Context errors

A

Measures do not automatically have a row context. “X” iterating functions can create a row context.

26
Q

Changing filter context

A

Filter context remains the same unless explicitly changed. FILTER functuion does not change filter context. ALL ignores the filter context, but does not change it.

27
Q

RELATED

A

Row context only includes fields in the current table. Must use RELATED to access further tables. Must be a relationship between tables. Can go through multiple relationships. Many to one relationships.

28
Q

Nested row contexts

A

Can use variables to grab value from current row, then use it in a calculation for all rows. Primarily calculated columns.

(Insert RankOnPrice formula from slides here)

29
Q

KEEPFILTERS

A

Used within CALCULATE so that filters in the report are maintained. Result will be an intersection of CALCULATE filters and report filter context.

CALCULATE(Sales amount,
KEEPFILTERS(Product[Color] IN {‘Red’, ‘White’, ‘Blue’})
)

30
Q

CALCULATE

A

Replaces / modifies filter context. Creates an implicit FILTER on an expression.
Can add multiple filters, result will be intersection of all the filters.
But nested CALCULATE functions overwrite each other. Innermost CALCULATE wins.
If need to work with two columns, use FILTER, ALL (to narrow down the number of columns in table), and KEEPFILTERS if needed.
Cannot use aggregators in compact CALCULATE statements, use a variable wherever possible to avoid using FILTER.
Calculate cannot modify a variable.
Can put ALL directly in a CALCULATE, don’t need to add a FILTER first.

31
Q

Table Functions

A

Return a table, not a value.

FILTER, ALL, VALUES, DISTINCT, RELATEDTABLE

32
Q

USERELATIONSHIP

A

Activates an inactive relationship. Used with Calculate. Relationship must already exist.

33
Q

CROSSFILTER

A

Changes the filter status of a relationship. Single, none, both. Much better than making a relationship always bidirectional.
allows use of a value from the one side of one to many relationship.

CALCULATE(COUNTROWS(Customers),
CROSSFILTER(related field 1, related field 2, BOTH)
))

34
Q

ALL + CALCULATE

A

When used with CALCULATE, ALL removes filters from the named table or field, respecting any other filters placed within CALCULATE. It does not necessarily overwrite. Should be called REMOVEFILTERS.

CALCULATE (
CALCULATE (ALL(Date[Year])
Date[Year] = 2007
)
Does not force the calculation to use all years, instead it removes any filters from Year.
35
Q

ALLSELECTED + CALCULATE

A

Gives the “visual total”, respecting filters from slicers in the report. Can be used with table, column, or nothing at all.
ALLSELECTED(table[column]) - could be used to generate % of column or row totals, depending on matrix structure.
ALLSELECTED(table)
ALLSELECTED() - can be used to obtain % of grand totals, but respecting slicer selections

36
Q

Context Transition

A

A byproduct of calculate.
Transforms row context into a filter context.
Can be used safely in tables with unique values, not in tables with duplicates.
Does not filter one row, filters all the identical rows.
Creates a filter context out of a row context.
Transforms all the row contexts, not just the last one.

Invoke by (calculated column):
Test = 
CALCULATE(
   SUM ('Product'[Unit Price]')
) < This will calculate the sum of all unique rows, deliver the value to each row
37
Q

Circular dependency

A

Can be created by context transition if multiple columns use CALCULATE in a table that does not have unique values. Won’t happen if use ALLEXCEPT to remove offending columns.

38
Q

CALCULATE execution order

A

1.It evaluates filter arguments, still in the original evaluation contexts (both row context and filter context)
2.It executes context transition
•Adds all the column filter to the new context
3.It evaluates the modifiers
•USERELATIONSHIP, CROSSFILTER, ALL
4.It applies the filter arguments evaluated in (1)

This code works:
Delivered amount 2007:
CALCULATE ( 
[Sales Amount],
'date'[Calendar Year] = "CY 2007",
USERELATIONSHIP (
Sales[DeliveryDateKey],
'Date'[DateKey]
))
39
Q

RANKX

A

o Table
•Evaluated in the external filter context
o Expression
•Evaluated in the row context of Table during the iteration
•Evaluated in the external filter context for the rank calculation

Using ALL, the lookup table contains all of the products and RANKX works as expected. ALLSELECTED is another good candidate (depending on requirements).

RankOnSales :=
RANKX (
ALL( Products ), [SumOfSales] )

RANKX somewhat relies on context transition.If the context transition is missing, then wrong values will be computed.

You need to force context transition, when needed, using CALCULATE. Using a measure reference does not have this requirement because it is implicit.
RankOnSales := 
RANKX (ALL ( Products ), 
CALCULATE ( 
SUM ( Sales[Sales] ) ))
40
Q

ISINSCOPE

A 
ISINSCOPE lets you detect when a column is currently being grouped by.It is useful when you want to control the calculation in different levels of a hierarchy.
RankOnSale = 
IF ( ISINSCOPE( Products[Product] ),
RANKX ( 
ALLSELECTED ( Products ), 
[SumOfSales]))
41
Q

Time intelligence functions

A

Table functions that simplify dealing with dates in DAX. They create a new table based on a few inputs.

42
Q

DATESYTD

A

Filters a table to the first day of the year through the last visible day. Can set the last day of the year to set the fiscal year end date. Can use in CALCULATE.

43
Q

TOTALYTD

A

Hides the use of CALCULATE. Can not accept additional filters. Not recommended to use.

44
Q

DATEADD

A

Shifts dates by specified number of specified intervals. Can be used to calculate YOY (or any period) changes.

45
Q

SAMEPERIODLASTYEAR

A

Simplifies DATEADD for YOY comparison.

46
Q

PARALLELPERIOD

A

similar to DATEADD, but always returns the full period. Could use to compare YTD sales to the full previous year’s sales as a percentage, for example.

47
Q

DATESINPERIOD

A

returns dates in a given number of periods. Used to calculate moving totals, averages.

CALCULATE (
SUM(Sales[SalesAmount]),
DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date]),
-1,
YEAR
)
)
48
Q

LASTDATE

A

returns a single row and a single column with a date.

49
Q

LASTNONBLANK

A

Returns last value in a column. Can be text or numeric.
(e.g., uses FIRST instead of last, but same principle:)
TopProduct :=
FIRSTNONBLANK (
TOPN (
1,
VALUES ( Product[Product Name] ),
[Internet Total Sales]
),
1
)

50
Q

Semi-Additive measures

A 
Finding last balance:
LastBalance =
VAR LastDayWithTransactions =
CALCULATE (
MAX (Balances[Date]),
ALL (Balances[Name])
)
RETURN
CALCULATE (
SUM (Balances[Balance]),
'Date'[Date] = LastDayWithTranactions
)

or

LastBalanceNonBlank =
SUMX (
VALUES (Balances[Name]),
CALCULATE (
SUM(Balances[Balance]),
LASTNONBLANK (
'Date'[Date],
CALCULATE (COUNTROWS (Balances))
)
)
)

Power BI (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions