Write DAX formulas for Power BI Desktop models

Question 1

What types of calculations can you add to your semantic model using DAX in Power BI?

Answer 1

Calculated tables, calculated columns, and measures.

Question 2

What is a calculated table in Power BI?

Answer 2

A table created using a DAX formula to duplicate, transform, or generate new data, which is imported into the model and increases storage size.

Question 3

What is the primary function of a calculated column in Power BI?

Answer 3

To add a column to any table with a DAX formula that evaluates for each row, increasing storage size and evaluated at data refresh for Import mode tables or at query time for DirectQuery tables.

Question 4

How are measures different from calculated columns in Power BI?

Answer 4

Measures are concerned with summarization, evaluated at query time, and their results are not stored in the model. They are identified with a calculator icon in the Fields pane.

Question 5

What is a role-playing dimension in Power BI?

Answer 5

A dimension table that plays multiple roles in a model, such as a Date table relating to multiple date columns in another table, using active and inactive relationships.

Question 6

What is the purpose of What-if parameters in Power BI?

Answer 6

To allow users to select or filter by values in a calculated table, enabling hypothetical analysis without the calculated table propagating filters.

Question 7

What is the general structure for creating a model calculation in DAX?

Answer 7

<Calculation> = <DAX>
</DAX></Calculation>

Question 8

What are the main elements used to assemble DAX formulas?

Answer 8

DAX functions, DAX operators, references to model objects, constant values, DAX variables, and whitespace.

Question 9

What is IntelliSense in Power BI Desktop?

Answer 9

A code-completion aid that lists functions and model resources, helping quickly build accurate formulas.

Question 10

What must follow function names in DAX formulas?

Answer 10

Parentheses, where variables are passed in.

Question 11

How should column references be formatted in DAX formulas?

Answer 11

Enclosed within square brackets and optionally preceded by the table name for clarity.

Question 12

How should measure references be formatted in DAX formulas?

Answer 12

Enclosed within square brackets, and it’s recommended not to precede them with the table name.

Question 13

What role does whitespace play in DAX formulas?

Answer 13

It helps format the formulas for readability and troubleshooting, without affecting logic or performance.

Question 14

How can you format a measure for better readability in DAX?

Answer 14

By using spaces, tabs, and carriage returns to structure the formula clearly.

Question 15

What are the model data types in Power BI?

Answer 15

Whole number, decimal number, boolean, text, date, currency, and BLANK.

Question 16

How are calculated column data types determined in Power BI?

Answer 16

They are inferred from the formula used.

Question 17

What is the range of values for a whole number in DAX?

Answer 17

-2^63 through 2^63-1.

Question 18

How does DAX handle database NULL values?

Answer 18

With the BLANK data type, which represents the absence of a value.

Question 19

What functions are related to the BLANK data type in DAX?

Answer 19

The BLANK() function returns BLANK, and the ISBLANK() function tests for BLANK.

Question 20

What is the IF function in DAX used for?

Answer 20

To test a condition and return one value if TRUE and another if FALSE.

Question 21

Name a few summarization functions available in both Excel and DAX.

Answer 21

SUM, COUNT, AVERAGE, MIN, MAX.

Question 22

What does the DISTINCTCOUNT function do in DAX?

Answer 22

Counts the number of distinct values in a column.

Question 23

Why should you use the DIVIDE function in DAX?

Answer 23

It handles division by zero cases and is optimized for performance.

Question 24

What is a recommended practice when dealing with potential zero or BLANK denominators in DAX?

Answer 24

Use the DIVIDE function for better performance and handling.

Question 25

What happens when you use arithmetic operators on BLANK values in DAX?

Answer 25

BLANK is treated as zero for arithmetic operations and as an empty string for concatenation.

Question 26

What is the purpose of using DAX variables in Power BI?

Answer 26

To improve readability, maintainability, and performance of formulas by evaluating variables only once and simplifying testing of complex formulas.

Question 27

How do you declare a variable in a DAX formula?

Answer 27

Use the VAR keyword followed by the variable name and its expression, then refer to it in the RETURN clause.