Add calculated tables and columns to Power BI Desktop models

Question 1

What is a calculated table in Power BI?

Answer 1

A table created using a DAX formula that generates new tables within the model.

Question 2

When should you use calculated tables?

Answer 2

When you need intermediate tables to simplify complex calculations or to create static tables for the model.

Question 3

How do you create a calculated table in Power BI?

Answer 3

By using the New Table feature and writing a DAX formula to define the table’s content.

Question 4

Give an example of a DAX formula for creating a calculated table.

Answer 4

Sales Summary = SUMMARIZE(Sales, Sales[Product], “Total Sales”, SUM(Sales[Sales Amount]))

Question 5

What is the first step to create a calculated column in Power BI?

Answer 5

Ensure the table in Data view is selected, then use the “New column” option in the Table tools ribbon.

Question 6

How do you create a fiscal year calculated column in DAX?

Answer 6

Due Fiscal Year = “FY” & YEAR(‘Due Date’[Due Date]) + IF(MONTH(‘Due Date’[Due Date]) > 6, 1)

Question 7

How can you define a calculated column for fiscal quarters?

Answer 7

Due Fiscal Quarter = ‘Due Date’[Due Fiscal Year] & “ Q” & IF(MONTH(‘Due Date’[Due Date]) <= 3, 3, IF(MONTH(‘Due Date’[Due Date]) <= 6, 4, IF(MONTH(‘Due Date’[Due Date]) <= 9, 1, 2)))

Question 8

What DAX formula converts a date to “yyyy mmm” format?

Answer 8

Due Month = FORMAT(‘Due Date’[Due Date], “yyyy mmm”)

Question 9

How do you create a MonthKey column in Power BI?

Answer 9

MonthKey = (YEAR(‘Due Date’[Due Date]) * 100) + MONTH(‘Due Date’[Due Date])

Question 10

What is row context in DAX?

Answer 10

Row context refers to the current row being evaluated in a table, allowing calculations to be performed on a row-by-row basis.

Question 11

How is row context automatically applied in DAX?

Answer 11

It is automatically applied in calculated columns and row-level calculations in tables.

Question 12

Can row context be applied to measures?

Answer 12

No, row context cannot be directly applied to measures, which work with filter context.

Question 13

How can row context be created in a measure?

Answer 13

By using functions like SUMX, FILTER, and RELATED, which iterate over rows.

Question 14

What are the three techniques to add columns to a model table in Power BI?

Answer 14

1. Add columns to a view or table in a relational database.
2. Add custom columns using M in Power Query.
3. Add calculated columns using DAX in model tables.

Question 15

Why is it recommended to add custom columns in Power Query?

Answer 15

They load to the model in a more compact and optimal way.

Question 16

When should you use a calculated column in Power BI?

Answer 16

When the formula depends on summarized model data or requires specialized DAX functions like RELATED or RELATEDTABLE.