Power BI Certification Exam Review

Comprehensive practice flashcards covering data modeling, DAX expressions, Power Query transformations, row-level security, visual optimization, and workspace management for Power BI exam review.

In the shown Power BI data model, what storage modes should be configured for the Customer, Date, Sales, and SalesAggregate tables to minimize visual load times and satisfy the refresh requirements?

• Customer: Dual
• Date: Dual
• Sales: DirectQuery
• SalesAggregate: Import

Which connector should you select to connect Power BI to a project management app fully hosted in Microsoft Teams and developed with Microsoft Power Apps?

Dataverse

You have two Excel tables in Power Query named Customer (with Customer ID and Address ID) and Address (with Address ID, City, State/Region, and Country). To create a single table containing one row per customer with address attributes, what operation should you perform?

Merge the Customer and Address tables.

In Power Query Editor, when merging ProductCategory, ProductSubCategory, and Product (where every Product has a ProductSubCategory, but not every ProductSubCategory has a parent ProductCategory), which join types ensure the best performance?

• Between Product (Left Table) and ProductSubCategory (Right Table): Inner
• Between ProductSubCategory (Left Table) and ProductCategory (Right Table): Left outer

Based on the provided Power Query column profile graphic for the State column, how many different values exist (including nulls), and how many non-null values occur only once?

• Different values in State including nulls (distinct count): $69$
• Non-null values that occur only once (unique count): $4$

When merging a Categories query into a Products query in Power Query Editor to produce a single consolidated Product table, what should you configure on the Categories query?

Disable the query load (uncheck 'Enable load').

What four actions must be performed in sequence to allow the Power BI service to connect to an on-premises Microsoft SQL Server database and refresh a dataset daily?

1. Configure an on-premises data gateway.
2. Add a data source.
3. Add the dataset owner to the data source.
4. Configure a scheduled refresh.

Which generic connector should you use in Power BI Desktop to connect to a Cassandra database when no native connector exists in the Get Data list?

ODBC

In Power Query Editor, what is the fastest way to replace values displaying as errors in a numeric discount column with a fixed value such as $0.05$ without deleting rows?

Select Replace Errors.

You have a CSV column named Logged containing timestamps formatted as '2018-12-31 at 08:59'. How should you transform the column to analyze complaints using a built-in date hierarchy?

Split the Logged column by using ' at ' as the delimiter.

How should table settings in Power BI Desktop be configured to make a dimension table available as an organizational data type in Microsoft Excel?

Set 'Is featured table' to Yes, define the Row label (e.g., Name), and specify the Key column (e.g., ID).

What relationship modification will improve query performance in a data model containing bidirectional cross-filters between dimension and fact tables?

Changing the Cross filter direction setting of the relationships from Both to Single.

Which DAX expression calculates the total sales from the equivalent month of the previous calendar year using a measure named [Total Sales] and a date table?

$$CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))$$

In a Power BI model, Role1 filters City[State Province] = 'Kentucky' and Role2 filters Date[Calendar Year] = 2020. If a user belongs to both roles, which data will they see?

The user will see data where the State Province value is Kentucky OR where the Calendar Year is 2020 (RLS filters combine using logical OR).

An employee table contains EmployeeID and ParentEmployeeID. All managerial chains lead up to the CEO. Which DAX expression calculates the count of management levels between an employee and the CEO?

$$PATHLENGTH(PATH(Employee[EmployeeID], Employee[ParentEmployeeID]))$$

Which DAX formula returns the top 8 products based on the highest value in [Total Sales] from a Product table?

$$TOPN(8, 'Product', 'Product'[Total Sales], DESC)$$

Which DAX functions and syntax create a contiguous date table containing 10 full calendar years ending with the current year?

$$\text{var var1 = YEAR(TODAY()) return CALENDAR(DATE(var1 - 9, 01, 01), DATE(var1, 12, 31))}$$

What DAX expression generates a single-column calculated table containing all integers from $-100$ to $100$?

$$GENERATESERIES(-100, 100, 1)$$

In a semi-additive inventory model recorded daily, which DAX expression returns the quantity available on the last recorded date of any selected period?

$$CALCULATE(SUM('Inventory'[QuantityAvailable]), LASTDATE('Date'[Date]))$$

How should visual interactions on a monthly sales column chart be configured so that clicking a segment in a bar chart displays the proportion that segment contributed to total monthly sales?

Set the visual interaction to Highlight.

What are the two primary field wells configured in a Key Influencers visual?

• Analyze: The metric or KPI being investigated (e.g., Total Sales or Attrition).
• Explain by: The dimension columns or attributes that drive or influence changes in that metric.

In Power BI Desktop, what setting must be applied to a column containing web image paths so that a table visual renders the actual image instead of the URL string?

Set the Data category of the column to Image URL.

Which chart type and axis configuration are required to use Power BI's automatic Anomaly Detection feature?

A Line chart with a continuous time-series date field placed in the Axis well.

When configuring bookmarks to allow users to toggle between two visuals on a page while preserving user-selected slicer values, which property must be disabled on both bookmarks?

Uncheck (disable) the Data property, while leaving the Display property enabled.

What feature should you use to receive automated notifications when a KPI or card metric on a Power BI dashboard exceeds a specified target value?

A Data Alert.

Under the principle of least privilege, which workspace role is required for a user to publish, unpublish, and manage permissions for a Power BI workspace App?

Member

In Power Query, what distinguishes a Step-level error from a Cell-level error?

• Step-level error: An error that fails the entire transformation step (e.g., a missing column referenced in code).
• Cell-level error: An error confined to specific cell values (e.g., an invalid mathematical or text conversion on specific row entries).

Which Power Query data source privacy level completely isolates a data source to prevent its data from leaking into or folding with queries from other sources?

Private

How can you make a single Power BI workspace App display public reports to all organizational users while restricting confidential reports to a specific security group?

Configure multiple App Audiences within the app settings and assign the security group to the audience containing the confidential reports.

If a scheduled dataset refresh is paused due to user inactivity in the Power BI service, which two actions reactivate the refresh schedule?

• Opening a report that consumes the dataset.
• Opening a dashboard that contains a pinned tile from the dataset.