Modeling the Data

Question 1

By using DAX, you can add 3 types of calculations to your data model:

Answer 1

• Calculated tables
• Calculated columns
• Measures

Question 2

How can DAX be used to enforce filters over model tables

Answer 2

By using RLS

Question 3

The formula can duplicate or transform existing model data or create a series of data to produce a new table and always imported into your model?

Answer 3

Calculated tables

Question 4

Cons of Calculated Tables

Answer 4

Increases model storage size and can prolong data refresh time

Question 5

Calculated tables are useful in

Answer 5

• Date tables
• Role playing dimensions
• What-if analysis

Question 6

What table is required to apply special time filters known as time inteligence

Answer 6

Date tables

Question 7

When your source data doesnt include a date table, you can create one as calculated tables by using the

Answer 7

CALENDAR or CALENDARAUTO function

Question 8

In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.
CALENDARAUTO() will return

Answer 8

all dates between January 1, 2010 and December 31, 2011.

Question 9

In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.
CALENDARAUTO(3) will return

Answer 9

CALENDARAUTO(3) will return all dates between April 1, 2010 and March 31, 2012.
*Hint: July(7)-3 = Apr(4)
June(6) -3 = March(3)

Question 10

What-if parameters allow report users to

Answer 10

select or filter by values that are stored in the calculated table. For example, awhat-ifparameter could allow the report user to select a hypothetical currency exchange rate, and a measure could divide revenue values (in a local currency) by the selected rate.

Question 11

Formula is evaluated for each table row and it returns a single value

Answer 11

Calculated Columns

Question 12

Concerned with achieving summarisation over model data and returns a single value.

Answer 12

Measures

Question 13

_____ are evaluated at query time and their results are never stored in the model

Answer 13

Measures

Question 14

Compound Measure

Answer 14

When a measure references one or more measures, it’s known as compound measure

Question 15

Implicit vs Explicit Measures

Answer 15

• Explicit: model calculations that are written in DAX
• Implicit measures: columns that can be summarised by visuls in simplistic ways like counts, sum, minimum, maximum, etc. Implicit measures are noted by $\sum$

Question 16

The DIVIDE function

Answer 16

TheDIVIDEfunction automatically handles division by zero cases. If an alternate result isn’t passed in, and the denominator is zero or BLANK, the function returns BLANK.

Question 17

You’re developing a data model in Power BI Desktop. You’ve just added a date table by using the CALENDARAUTO function. You’ve extended it with calculated columns, and you’ve related it to other model tables. What else should you do to ensure that DAX time intelligence calculations work correctly?
A. Add time intelligence measures to the date table
B. Mark as a Date table
C. Add a fiscal hierarchy
D. Add a date column

Answer 17

Mark as a Date table so that Power BI can correctly filter its dates.

Question 18

Difference between calculated column and a measure

Answer 18

• Calculated columns extend a table with a new column, while measures define how to summarize model data.
• Calculated columns are evaluated by usingrow contextat data refresh time, while measures are evaluated by usingfilter contextat query time.
• Calculated columns (in Import storage mode tables) store a value for each row in the table, but a measure never stores values in the model.
• Calculated columns (like any column) can be used to filter, group, or summarize (as an implicit measure), whereas measures are designed to summarize

Question 19

Row context

Answer 19

The formula for a calculated column is evaluated for each table row.

• Row context doesn’t extend beyond the table. If your formula needs to reference columns in other tables, you have 2 options
  
  • f the tables are related, directly or indirectly, you can use the[RELATED]or[RELATEDTABLE]DAX function. TheRELATEDfunction retrieves the value at the one-side of the relationship, while theRELATEDTABLEretrieves values on the many-side. TheRELATEDTABLE`function returns a table object.
• When the tables aren’t related, you can use theLOOKUPVALUE

Question 20

Using DAX Time Intelligence Functions …

Answer 20

simplifies the task of modifying date filter context

Question 21

Date Table Requirement:

Answer 21

• It must have a column of data type Date (or date/time), known as thedate column.
• The date column must contain unique values.
• The date column must not contain BLANKs.
• The date column must not have any missing dates.
• The date column must span full years. A year isn’t necessarily a calendar year (January-December).
• The date table must be indicated as a date table

Question 22

DATESYTD

Answer 22

Returns a single-column table that contains dates for the year-to-date (YTD) in the current filter context.

Question 23

DATESBETWEEN

Answer 23

Returns a table that contains a column of dates that begins with a given start date and continues until a given end date.

Question 24

DATESINPERIOD

Answer 24

Returns a table that contains a column of dates that begins with a given start date and continues for the specified number of intervals.

Question 25

Define Rules

Answer 25

Row context means the expression is evaluated for each row using the column values of that row. When the expression returns TRUE, the user can “see” the row. You can define rules that are either static or dynamic

Question 26

‘Region’[Region] = “Midwest”

Answer 26

Static Rules

Question 27

Dynamic Rules

Answer 27

uses specific DAX functions that return environmental values

Question 28

USERPRINCIPALNAME

Answer 28

USERPRINCIPALNAME function always returns the user in the user principal name format.

Question 29

Object level security (OLS) can restrict...

Answer 29

access to specific tables and columns and secure their metadata

Question 30

Restrictions in OLS

Answer 30

- You cannot mix RLS and OLS in the same role - If you need to apply RLS and OLS in the same model, create separate roles dedicated to each type.

Question 31

Good Modeling Practices on the number of datasets, number of roles, rules, model design, USERPRINCIPALNAME/USERNAME

Answer 31

- Strive to define fewer datasets (data models) with well-designed roles. - Strive to create fewer roles by using dynamic rules. A data-driven solution is easier to maintain because you don’t need to add new roles. - When possible, create rules that filter dimension tables instead of fact tables. It will help to deliver faster query performance. - Validate that the model design, including its relationships and relationship properties, are correctly set up. - Use the `USERPRINCIPALNAME` function instead of `USERNAME` function. It provides consistency when validating the roles in Power BI Desktop and the Power BI service. - Rigorously validate RLS and OLS by testing all roles. - Ensure that the Power BI Desktop data source connection uses the same credentials that will be applied when set up in the Power BI service.

Question 32

Star Schema Design

Answer 32

- presents a user-friendly structure and supports high performance analytic queries - it classifies model tables as either fact or dimension. - The fact table forms the center of a star and the dimension table represents the points of the star

Question 33

Fact Tables

Answer 33

- store an accumulation of rows that represent observations or event sthat record a specific business activity - It is not GCP to relate a fact table directly to another fact table

Question 34

Dimension Tables

Answer 34

- describe your business entities - The columns in dimension tables allow filtering and grouping of fact table data - Each dimension table must have a unique column which is referred to as its key column - Dimension tables can be used to filter multiple fact tables and fact tables can be filtered by multiple dimension tables.

Question 35

Analytic Queries

Answer 35

produces a result from a data model. Each power bi visual in the background submits an analytic query to Power BI to query the model

Question 36

An analytic query has 3 phases that are implemented in the following order:

Answer 36

1. Filter 2. Group: divides query results into groups 3. Summarise: produces a single value result. Typically numeric columns are summarised by using summarisation methods. These methods are simple summarisation.

Question 37

3 Different Model Resources that are fields to configure visuals which in the background configures an analytic query:

Answer 37

- Columns: Filter, Group and Summarise - Hierarchy levels: Filter and Group - Measures: Filter and Summarise (cannot be used to group data)

Question 38

Table Storage Mode:

Answer 38

- Import – Queries retrieve data that’s stored, or cached, in the model. - DirectQuery – Queries pass through to the data source. - Dual – Queries retrieve stored data or pass through to the data source. Power BI determines the most efficient plan, striving to use cached data whenever possible.