Brainscape's Knowledge GenomeTM

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


See full index

Power BI > Model - Create measures using DAX > Flashcards

Model - Create measures using DAX Flashcards

1
Q

What is DAX?

A

Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel.

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

What do DAX formulas include?

A

DAX formulas include functions, operators, and values to perform advanced calculations and queries on data in related tables and columns in tabular data models.

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

Why is DAX important?

A

Feature engineering: DAX allows you to augment the data that you bring in from different data sources by creating a calculated column that didn’t originally exist in the data source.

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

What is a calculated column?

A

Performed in Power BI’s data model
A calculated column is a column that you add to an existing table (in the model designer) and then create a DAX formula that defines the column’s values.
When a calculated column contains a valid DAX formula, values are calculated for each row as soon as the formula is entered.
Values are then stored in the in-memory data model.

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

How to create a calculated column?

A

(…) on a field and then New Column

Enter the formula (i.e., value on left side of = sign is the column name the information on the right hand side is the DAX expression)

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

What are the implications of calculated columns?

A

Calculated columns are materialized in the .pbix Power BI file extension, meaning that each time you add a calculated column, you are increasing the size of the overall file.

Having too many calculated columns will slow performance and will cause you to reach the maximum Power BI file size sooner.

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

What is best practice around calculated columns?

A

Use SQL: Using SQL language is an efficient way of creating a column because it would make the data source do the calculations for you. In Power BI, the calculated column would appear like any other column.

When you create a calculated column by using DAX, you do not need to refresh the dataset to see the new column. In the other methods, you would need a refresh to see changes. This process can be lengthy if you are working with a lot of data. However, this issue is irrelevant because, after columns have been created, they are rarely changed.

Does not Compress well: The DAX calculated column does not compress as well as the other methods. The other column types do get compressed, which makes the .pbix file smaller and the performance is usually faster.

Create Columns Early: Generally, the earlier you can create a column, the better.
Find alternatives first: It is not considered an optimal practice to use DAX for calculations if you can use a different mechanism.

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

What is a custom column?

A

Performed in Power Query
This allows you to create calculated columns using your existing data. You can give it a name and then build a formula by double clicking on fields in the field list.

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

What are the different ways to create a custom column? What Languages do they use?

A

Create the column in the source query when you get the data (n.b., Each data source would have a different technique for completing this action) = database language

Create a custom column in Power Query = M language

Create a calculated column by using DAX in Power BI desktop.

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

What is a measure?

A

Measures are dynamic calculation formulas where the results change depending on context.
Measures are used in reporting that support combining and filtering model data by using multiple attributes such as a Power BI report or Excel PivotTable or PivotChart.
Measures are created by using the DAX formula bar in the model designer.

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

When to use a measure?

A

You can build a measure without writing DAX code; Power BI will write it for you when you create a quick measure.

Aggregation operates over the entire dataset

Want to slice and dice the data by other criteria

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

How to create a quick measure?

A

(…) and then select quick measure

***Power BI creates the DAX measure for you and displays the DAX. This approach can be a helpful way to learn the DAX syntax.

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

How to create a measure?

A

Fields list, click the three-dot ellipsis on the selected field and select New measure.
Text will now appear in the formula bar underneath the ribbon.

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

Difference between quick measure and measure?

A

A quick measure does not require the formula bar and transforms an existing column whereas a measure uses the formula bar and creates a new column/field

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

What is the difference between a calculated column and a measure?

A

Calculated column
The fundamental difference between a calculated column and a measure is that a calculated column creates a value for each row in a table.
For example, if the table has 1,000 rows, it will have 1,000 values in the calculated column.
Calculated column values are stored in the Power BI .pbix file.
Each calculated column will increase the space that is used in that file and potentially increase the refresh time.
Calculated columns use disk space and RAM

Measure
Measures are calculated based on the filters that are used by the report user. These filters combine to create the filter context.
Measures are used for Aggregation.
This is because measures only use CPU
Changes with the filter context (e.g., tables, fields and value changes when using filters)
Every calculated measure must contain an aggregation function such as AVG or SUM

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

What is meant by context in Power BI?

A

The unique set of tables, fields and values which determine DAX measure results

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

How does context affect DAX measures?

A

With Power BI, even though the measure was only defined once, it can be used in these visuals in different ways.

It is the context of how the DAX measure is used that calculates these totals accurately.
Interactions between visuals will also change how the DAX measure is calculated.

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

What factors affect DAX calculations?

A

Many other factors affect how DAX formulas are evaluated. Slicers, page filters, and more can affect how a DAX formula is calculated and displayed.

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

What is the calculate function?

A

The CALCULATE function is your method of creating a DAX measure that will override certain portions of the context that are being used to express the correct result.

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

How should you name calculate functions?

A

When you use the CALCULATE function to override the context, it is helpful to name the measure in a way that describes exactly how you are overriding it.

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

What is USERELATIONSHIP?

A

This function is used to specify a relationship to be used in a specific calculation and is done without overriding any existing relationships.

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

Why is USERELATIONSHIP important?

A

It is a beneficial feature in that it allows developers to make additional calculations on inactive relationships by overriding the default active relationship between two tables in a DAX expression, as shown in the following example:

Sales by Ship Date = CALCULATE(Sum(Sales[TotalPrice]), USERELATIONSHIP(Sales[ShipDate],’Calendar’[Date]))

Now, you will be able to create the second visual.

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

What is a semi-additive measure?

A

Semi additive measures, i.e. measures that have to be aggregated differently over different dimensions, are commonly used in BI solutions. One example could be stock levels. Of course we don’t want to sum them up over time, but only over product, location etc. For the time, a different aggregation is used, for example average or last value.

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

What is standard evaluation behaviour?

A

Apply the measure across the whole context

25
Q

When should you change standard evaluation?

A

In situations where you don’t want the standard evaluation behaviour in Power BI, you can use the CALCULATE and/or USERELATIONSHIP functions.

More circumstances exist where you don’t want the standard behaviour.

26
Q

What are standard measures?

A

Standard measures are simple concepts, where they might use the SUM, AVERAGE, MIN, and MAX functions.

27
Q

When to use semi-additive measures?

A

Occasionally, summing a measure doesn’t make sense: For example, if on Monday, you have 100 mountain bikes, and on Tuesday you have 125 mountain bikes, you wouldn’t want to add those together to indicate that you had 225 mountain bikes between those two days.

you would need to tell Power BI not to add the measure but instead take the last value for the month of March and assign it to any visual.

This approach will stop the SUM from crossing all dates. Instead, you will only use the SUM function on the last date of the time period, thus effectively creating a semi-additive measure.

28
Q

What is time intelligence?

A

Data Analysis Expressions (DAX) includes time-intelligence functions that enable you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.

29
Q

Why is time intelligence important?

A

All data analysts will have to deal with time. Dates are important, so we highly recommend that you create or import a dates table. This approach will help make date and time calculations much simpler in DAX.
While some time calculations are simple to do in DAX, others are more difficult.

30
Q

Why would you need to write DAX formulas?

A
  • Writing formulas is an important skill that allows you to perform much more sophisticated analysis
31
Q

Why might you write a measure?

A
  • More sophisticated calculations possible
  • More specific analyses can be achieved
  • A calculated column increases the model size whereas measures don’t
32
Q

What is implicit measures?

A
  • These are measures which happen automatically in Power BI (e.g., Aggregate columns in a table)
33
Q

Why is it best to create explicit measures?

A
  • Implicit measures provide unexpected results in some cases due to the Summarise by column property (e.g., may set summarisation to sum which would not produce meaningful results in a visual)
  • Explicit measures can be reused in other measures (i.e., write less code therefore more manageable)
  • If you connect to your Power BI dataset from Excel you cannot use implicit measures
  • Implicit measures cannot leverage inactive relationships
  • Implicit measures not supported by calculation groups
34
Q

How are measures different from calculated columns?

A
  • Immediacy: can see results of calculated column immediately whereas measures are not shown until you use a visual (this allows your measures to return different results depending on the filters used)
  • Applicability: calculated columns apply the calculation to each row of a table whereas measures work on columns and tables not specific rows (measures most often use DAX aggregation function)
35
Q

How can you move a measure?

A
  • Report view > Select measures > Measure tools > Home table….select table where measure should be stored
36
Q

Why would you use DIVIDE or / operator?

A

DIVIDE

  • Use the DIVIDE function to avoid division by zero
  • Use this when the expression could return a zero or blank

/ Operator
- In the case that the denominator is a constant value, we recommend that you use the divide operator. In this case, the division is guaranteed to succeed, and your expression will perform better because it will avoid unnecessary testing.

37
Q

How might you describe a formula that results in a performance decrease?

A
  • The performance loss is significant since the formula is expensive (computationally expensive)
38
Q

How can you format measures?

A
  • Measures are displayed in Power BI’s default format settings
  • Measure tools > Formatting
  • Best practice to format a measure as soon as it is created
  • Where possible do not use table names in your measures
39
Q

What are variables?

A
  • Variables are a feature in DAX that allows you to avoid repeating yourself
  • They are essentially calculations within a measure
40
Q

Why are variables important?

A
  • Complex DAX formulas are computationally expensive

- Because variables are only evaluated once you will use less code and save resource

41
Q

What are the main uses of the CALCULATE function?

A
  • 1) Can be used to alter relationships when paired with other DAX measures (e.g., USERRELATIONSHIP and CALCULATE can activate inactive relationships)
  • 2) Allows you to alter the filter context: so you can add, remove or update filters
42
Q

What are the CALCULATE parameters?

A
  • It accepts a scalar expression as its first parameter and the subsequent parameters are filter arguments
  • Using CALCULATE with no filter arguments is only useful for context transition
43
Q

Explain how you can add filters to CALCULATE?

A

Adding filters
- One value = Table[Column] = “Value 1”
- Multiple values = Table[Column] = {“Value 1”, “Value 2”, “Value 3”}
- Multiple Column = Table[Column] = “Value”,
Table2[Column] = “Value”

44
Q

Explain how you can remove filters from CALCULATE?

A

Several DAX functions can be used as CALCULATE modifiers to ignore filters (ALL, ALL-EXCEPT, ALLSELECTED)

 ALL
 Can remove filters from: 
 a) one or more columns of same table 
 b) an entire table 
 c) entire data model
  • If you are removing filters from a column that is sorted by another column you should remove filters from both columns (otherwise you could get unexpected results)
45
Q

Explain how you can update filters?

A
  • You can add further filters to further modify the context (e.g., already made an ‘ignore’ filter and now you add a further filter)
46
Q

What is context transition?

A
  • This is when you transfer from row context to filter context (i.e., takes all values from all other columns and uses them as filters)

Row context

  • Understood as the current row
  • This is present in calculated columns and iterators (functions that evaluate a table row by row)
  • FILTER is an iterator (evaluates filter condition for each row)
  • Rows that satisfy the function are included in the result

Filter context

  • Understood as all applied filters
  • Filters can come from slicers, filter pane or by select visual elements
  • Filters can be applied programmatically using DAX
47
Q

Explain context transition?

A

This happens when you don’t supply a filter parameter for calculate

Calculate column = will sum all the numbers in a column and use the aggregation in every row (if you are not using any filters)
CALCULATE = performs context transition which takes all values from all other columns and uses them as filters (evaluates all values in all columns)

Example
A 1 Sum = 1 A 1 Sum = 1
B 2 Sum = 2 B 2 Sum = 4
C 3 Sum = 3 B 2 Sum = 4

48
Q

Name a drawback with using Time Intelligence with DAX?

A
  • DAX only uses the Gregorian calendar

- You will have to use a custom calculation for a different calendar (e.g., retail or weekly)

49
Q

What are Time Intelligence functions?

A
  • Enables report builders to aggregate metrics across time (e.g., year to date)
50
Q

How to use Time Intelligence functions?

A
  • Create a date table
  • Date type column with unique values
  • Mark as official data table
51
Q

How many time-intelligence functions are there in DAX can you name some?

A
  • over 30
52
Q

Why would you replace numeric columns with measures?

A
  • Only applies to columns that contain some sort of calculation (e.g., Total sales, Profit, Sales before tax etc)
  • Can reduce these columns to reduce the size of the data model
53
Q

How can you replace numeric columns with measures?

A
  • 1) First reference the columns you wish to delete and then build upon them
  • 2) Create measure
54
Q

What are iterator statistical functions?

A
  • All statistical functions apart from DISTINCTCOUNT have an iterator function
  • These have an X as a suffix
  • They take two parameters = 1) table to iterate through 2) expression to evaluate each row
55
Q

How many statistical functions are there in DAX can you name some?

A
  • over 60
56
Q

What are statistical functions?

A
  • Explicit measures
  • Can be used to build-on and create more complex measures
  • All statistical functions take a column as a reference and produce a scalar value
57
Q

What are the three types of measures?

A

Additive = measures that are aggregated using the sum function across any dimension (e.g., revenue across different products)

Semi-additive = can be added across some but not all dimensions specifically not dates (e.g., increments of sales taken on a day-by-day basis…like compound interest)

Non-additive = these measures cannot be added up across any dimensions (e.g., cannot add average prices across dimensions as would not make sense)

58
Q

Name some semi-additive measures? What are best?

A
  • All depends on the business requirements

Power BI (8 decks)

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