Analyzing Data with Microsoft Power BI Flashcards

You have sales data with a 'Ship Date' and an 'Order Date'. You want to calculate metrics based on both dates. What model design approach should you use?

Answer: Use a single date table with two relationships, making one active and one inactive

The standard pattern is one date table related to both date columns, with one relationship active and the other inactive, using USERELATIONSHIP in measures to switch context.

Which DAX function returns the value from a previous row in a sorted table, commonly used for period-over-period comparisons within a table visual?

Answer: OFFSET

OFFSET is a DAX window function (introduced in 2022) that returns a value from a row that is a specified number of positions before or after the current row in a sorted partition.

A Power BI dataset has row-level security (RLS) configured. Who can see all rows regardless of RLS rules when accessing the dataset in Power BI service?

Answer: Workspace Admins and Members (dataset owners)

Workspace Admins and Members who own the dataset are not subject to RLS; only users assigned to RLS roles (typically Viewers) have row-level restrictions applied.

What is the main advantage of using DirectQuery mode instead of Import mode in Power BI?

Answer: Data is always current because queries go directly to the source

DirectQuery sends queries to the source database in real time, ensuring data is always up-to-date without requiring scheduled refreshes.

Which Power BI feature would you use to let different users see the same report but with data automatically filtered to their own region?

Answer: Dynamic row-level security with USERNAME() or USERPRINCIPALNAME()

Dynamic RLS uses DAX functions like USERNAME() or USERPRINCIPALNAME() to compare the logged-in user against a security table and filter data accordingly.

In Power BI, what does the 'Analyze in Excel' feature allow a user to do?

Answer: Connect Excel to the Power BI dataset using PivotTables and PivotCharts

Analyze in Excel creates a live connection from Excel to the Power BI dataset, allowing users to build PivotTables and PivotCharts against the same data model.

What is the purpose of the ALLEXCEPT function in DAX?

Answer: Removes all filters from a table except those applied to specified columns

ALLEXCEPT removes all context filters from a table except for filters on the columns you explicitly list, useful for ratio calculations that need to preserve certain filter context.