Courseiva

CCNA Visualize and analyze the data Questions

18 of 243 questions · Page 4/4 · Visualize and analyze the data · Answers revealed

Practice these questionsDomain overviewAll questions
226
MCQmedium

You have a Power BI report that uses a date hierarchy (Year, Quarter, Month) on the axis of a line chart. Users want to drill down from year to quarter to month. However, the drill-down feature is not working. What is the most likely cause?

A.The date field is placed in the Axis field well without creating a hierarchy.
B.The line chart does not support drill-down functionality.
C.The date table is not marked as a date table in the model.
D.Cross-filtering from another visual is interfering.
AnswerA

Drill-down requires explicit hierarchy in the field well.

Why this answer

Option C is correct because the drill-down feature requires a hierarchy in the field well. If only the date field is used without explicit hierarchy, drill-down is disabled. Option A is wrong because the line chart supports drill-down with hierarchies.

Option B is wrong because cross-filtering does not disable drill-down. Option D is wrong because drill-down does not require a date table relationship.

Practice this question →
227
Multi-Selecthard

Which TWO DAX functions can be used to create a dynamic title that shows the currently selected year in a report, assuming a date table with a Year column?

Select 2 answers
A.SUM('Date'[Year])
B.DISTINCT('Date'[Year])
C.CONCATENATEX(VALUES('Date'[Year]), 'Date'[Year], ", ")
D.SELECTEDVALUE('Date'[Year])
E.CALCULATE(VALUES('Date'[Year]))
AnswersC, D

Joins multiple selected years into a string.

Why this answer

Options A and C are correct. SELECTEDVALUE returns the single selected value from a column; if multiple values are selected, it returns blank or a default. CONCATENATEX can be used to combine multiple selected years into a string.

Option B is wrong because CALCULATE does not return a text value. Option D is wrong because SUM is mathematical. Option E is wrong because DISTINCT returns a table, not a scalar.

Practice this question →
228
MCQmedium

You are creating a Power BI report that includes a scatter chart with many data points. Users want to identify outliers quickly. Which approach should you recommend?

A.Add a trend line to the scatter chart and use analytics pane to highlight outliers
B.Use a line chart to see spikes
C.Add a table with conditional formatting to show high values
D.Convert to a histogram to see distribution
AnswerA

Trend line and outlier detection in analytics pane directly identify outliers.

Why this answer

Option C is correct because adding a trend line and highlighting outliers helps identify them. Option A is wrong because a table is not visual. Option B is wrong because a line chart is for trends.

Option D is wrong because a histogram shows distribution, not outliers individually.

Practice this question →
229
MCQeasy

A company has a dataset with a table 'Orders' containing columns: OrderDate, CustomerID, Amount. They want to create a visual that shows the total amount per month. Which of the following is the best approach?

A.Create a pie chart with OrderDate as the legend.
B.Create a line chart with OrderDate on the axis and use the date hierarchy to drill down to month.
C.Create a table visual with OrderDate and Amount, then group by month in the visual.
D.Create a bar chart with a calculated column 'Month' extracted from OrderDate and use that as the axis.
AnswerB

Date hierarchies provide built-in drill-down capabilities and are the recommended approach.

Why this answer

Option B is correct because it leverages Power BI's built-in date hierarchy, which automatically groups OrderDate by year, quarter, month, and day. By placing OrderDate on the axis of a line chart and drilling down to the month level, you get an accurate monthly aggregation of Amount without needing any manual data transformation or calculated columns. This approach is efficient, maintains the underlying data model's integrity, and allows for easy drill-up/drill-down navigation.

Exam trap

The trap here is that candidates often think extracting a month column manually (Option D) is the most straightforward approach, but the exam tests whether you understand that Power BI's built-in date hierarchy is the optimal and intended method for time-based aggregations, avoiding unnecessary calculated columns.

How to eliminate wrong answers

Option A is wrong because a pie chart with OrderDate as the legend would treat each unique date as a separate slice, not aggregate by month, resulting in a cluttered and meaningless visual. Option C is wrong because table visuals in Power BI do not support grouping by month directly within the visual; you would need to create a calculated column or use a date hierarchy to achieve monthly aggregation. Option D is wrong because while a calculated column 'Month' extracted from OrderDate would work, it is not the 'best' approach—it adds unnecessary complexity, breaks the date hierarchy, and prevents easy drill-down to lower time granularities like day or quarter.

Practice this question →
230
MCQeasy

You are creating a Power BI report that includes a matrix visual showing sales by product category and year. You want to highlight the highest sales value in each category. Which conditional formatting option should you use?

A.Color by color scale with a diverging gradient.
B.Icons with a traffic light icon set.
C.Color by rules with a rule to highlight the maximum value per category.
D.Data bars with a bar color.
AnswerC

Rules allow conditional formatting based on specific conditions.

Why this answer

Option A is correct because 'Color by rules' can set a rule to highlight the maximum value per category. Option B is wrong because 'Color by color scale' applies a gradient, not highlighting the max. Option C is wrong because 'Data bars' show bars, not highlight max.

Option D is wrong because 'Icons' replace values with icons.

Practice this question →
231
MCQmedium

You have a Power BI report that uses a composite model with both Import and DirectQuery sources. You need to ensure that a measure using SUMX performs well. What should you do?

A.Disable relationships between tables
B.Use CALCULATE instead of SUMX
C.Create a calculated column in the source system to pre-aggregate values
D.Replace SUMX with SUM and adjust the model
AnswerC

Avoids iterating over DirectQuery rows.

Why this answer

In composite models, iterating over DirectQuery tables can be slow. Option C is correct: move the calculation to the source (e.g., SQL view). Option A is wrong because reducing cardinality may not help.

Option B is wrong because disabling relationships is not practical. Option D is wrong because changing to SUM might change logic.

Practice this question →
232
MCQhard

A Power BI report uses a composite model with a DirectQuery source and an imported table. The imported table has many rows, and the report is taking a long time to refresh. What is the most likely cause?

A.Measures are using CALCULATE with multiple filter conditions
B.The imported table is set to Dual storage mode, causing dependency on the DirectQuery source
C.The DirectQuery table is set to Import storage mode
D.The report contains many complex visuals that need to be rendered
AnswerB

Dual mode tables can trigger queries to the source during refresh, slowing it down.

Why this answer

In a composite model, when an imported table is set to Dual storage mode, it behaves as both Import and DirectQuery. During refresh, Power BI must check the DirectQuery source for consistency, which can significantly slow down the refresh process, especially with large imported tables. This dependency on the DirectQuery source is the most likely cause of the long refresh time.

Exam trap

The trap here is that candidates often assume long refresh times are caused by complex visuals or measures, but the question specifically targets the refresh process, where Dual storage mode's dependency on the DirectQuery source is the key performance bottleneck.

How to eliminate wrong answers

Option A is wrong because measures using CALCULATE with multiple filter conditions affect query performance at report rendering time, not the data refresh process. Option C is wrong because setting a DirectQuery table to Import storage mode would convert it to an imported table, which would not cause a dependency on the DirectQuery source during refresh; the issue described is about an imported table slowing down refresh. Option D is wrong because complex visuals affect report rendering performance, not the data refresh operation.

Practice this question →
233
MCQhard

You are reviewing a Power Query M script that imports sales data. The script filters orders from 2023, groups by CustomerID to sum SubTotal, sorts descending, and takes the top 10 customers. However, the query fails with an error. What is the most likely cause?

A.The parameters in Sql.Database are reversed; the correct order is database name first, then server.
B.The Table.FirstN function is missing the second argument; it should be Table.FirstN(#"Sorted Rows", 10).
C.The column name 'SubTotal' is misspelled; the correct column name is 'Subtotal'.
D.The #date function cannot be used with a datetime column; use DateTime.From instead.
AnswerC

Common typographical error; column names are case-sensitive in some databases.

Why this answer

Option C is correct because the error is most likely caused by a misspelled column name in the M script. Power Query is case-sensitive for column names, so 'SubTotal' does not match the actual column 'Subtotal', causing a runtime error when the script tries to reference the non-existent column.

Exam trap

The trap here is that candidates may overlook case sensitivity in Power Query M and assume column names are case-insensitive like in SQL Server, leading them to dismiss a simple typo as the root cause.

How to eliminate wrong answers

Option A is wrong because the Sql.Database function in Power Query M expects the server name as the first argument and the database name as the second, not reversed. Option B is wrong because Table.FirstN can accept a single argument (the table) and defaults to returning the first row; the script already includes the second argument '10' implicitly or explicitly, so missing it would not cause the described error. Option D is wrong because the #date function can be used with a datetime column by extracting the date part or comparing directly; the error is not related to type incompatibility with #date.

Practice this question →
234
Multi-Selecteasy

Which TWO of the following are valid ways to share a Power BI report with external users who do not have a Power BI license? (Choose two.)

Select 2 answers
A.Publish the report to a Power BI app and grant access to external users if the tenant settings allow.
B.Export the report to PDF and email it to the external user.
C.Use the 'Publish to web' option to create an embed code.
D.Share the report directly from the Power BI service by entering the external user's email address.
E.Add the external user to a distribution group and share the report with that group.
AnswersA, C

Power BI apps can be shared with external users if configured.

Why this answer

Options B and C are correct. Option B allows external users to view reports with a Pro license if the tenant allows. Option C allows sharing via a link that works without a license (but requires appropriate settings).

Option A is wrong because sharing directly requires the recipient to have a Pro license. Option D is wrong because exporting via email is not a built-in feature. Option E is wrong because sharing to a distribution group still requires licenses.

Practice this question →
235
MCQeasy

You are a Power BI analyst at a retail company. The company has a Power BI dataset that imports daily sales data from an on-premises SQL Server database. The dataset includes tables: 'Sales' (columns: SaleID, ProductID, SaleDate, Quantity, UnitPrice, CustomerID), 'Products' (ProductID, ProductName, Category), and 'Customers' (CustomerID, CustomerName, Region). The report needs to display a matrix visual showing total sales amount (Quantity * UnitPrice) by Category (rows) and Year (columns). The report currently shows blank values for the matrix. You have verified that relationships exist between the tables and that there is a date table marked as a date table. The measure used is: Total Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]). When you test the measure in a card visual, it returns a value. However, the matrix shows blanks. What is the most likely cause and solution?

A.Use the ALL function in the measure to ignore filter context.
B.Create an active relationship between the date table and the Sales table on the SaleDate column.
C.Modify the measure to use CALCULATE with USERELATIONSHIP to force the relationship.
D.Convert the SaleDate column to a date data type using Power Query.
AnswerB

Without an active relationship, the date table filters do not propagate to Sales, causing blanks in the matrix.

Why this answer

The matrix visual shows blanks because the date table is not actively related to the Sales table on the SaleDate column. Even though a date table is marked as a date table, without an active relationship, the filter context from the date table (used for Year in columns) does not propagate to the Sales table, causing the measure to return blanks in the matrix. Creating an active relationship between the date table and Sales[SaleDate] allows the Year filter to correctly filter the Sales rows, enabling the SUMX measure to compute total sales per category per year.

Exam trap

The trap here is that candidates assume marking a table as a date table automatically creates relationships with all date columns in the model, but in reality, the relationship must be explicitly created and set as active for filters to flow correctly.

How to eliminate wrong answers

Option A is wrong because using the ALL function would remove all filter context, including the Category and Year filters, causing the measure to return the grand total in every cell, not fixing the blank issue. Option C is wrong because USERELATIONSHIP is used to activate an inactive relationship, but here no relationship exists between the date table and Sales table; the problem is the absence of any relationship, not an inactive one. Option D is wrong because converting SaleDate to a date data type is unnecessary; the column is already a date type (as it is used in a date table relationship) and the issue is relational, not data type conversion.

Practice this question →
236
Multi-Selectmedium

Which TWO of the following are true about using AI visuals in Power BI? (Select two.)

Select 2 answers
A.The Copilot feature is available in all Power BI licenses
B.The Key Influencers visual is a native visual available without AI
C.Smart Narratives is a custom visual from AppSource
D.The Q&A visual allows users to type natural language questions
E.The Key Influencers visual uses machine learning to identify factors that affect a metric
AnswersD, E

Q&A is an AI-powered visual that interprets natural language.

Why this answer

Option A is correct: Q&A allows natural language queries. Option D is correct: Key Influencers visual uses machine learning to analyze factors. Option B is wrong: Decomposition Tree is an AI visual, not a native visual.

Option C is wrong: Smart Narratives is a visual, not a custom visual. Option E is wrong: Copilot is a feature that can be enabled, but it's not a visual type.

Practice this question →
237
MCQeasy

Your Power BI report includes a bar chart showing monthly sales. Users complain that the bars are too crowded when viewing a full year. What is the best design change to improve readability?

A.Sort the bars by sales value descending
B.Switch to a line chart
C.Remove the axis labels
D.Add a slicer for quarter to filter the view
AnswerD

A slicer lets users view one quarter at a time, reducing clutter.

Why this answer

Sorting by sales value does not reduce crowding, changing to a line chart is better for continuous data, and removing axis labels harms readability. Slicing by quarter reduces the number of bars visible at once.

Practice this question →
238
MCQeasy

You have the measure shown. You add it to a table visual alongside columns from a related table. The total is higher than expected. What is the most likely cause?

A.The Sales table is summarized at a different granularity.
B.The measure is not filtered by any slicer.
C.The visual is expanding the measure due to a many-to-one relationship.
D.The measure should use SUMX instead of SUM.
AnswerC

Cross-filtering can cause inflated sums.

Why this answer

Option B is correct because if the relationship is one-to-many and the visual includes columns from the one side, the measure might be summing across multiple rows due to cross-filtering. Option A is wrong because there is no filter. Option C is wrong because it's a sum.

Option D is wrong because the table is not summarized.

Practice this question →
239
MCQeasy

You want to create a visual that shows the top 5 products by sales amount, but also want to include an 'Other' category that aggregates all remaining products. Which approach should you use?

A.Add a slicer to filter top 5 and manually add 'Other'
B.Create a calculated column that assigns 'Other' to non-top-5 products
C.Apply a Top N filter and add a measure for 'Other'
D.Use the 'Group' feature on the product field and select 'Include Other'
AnswerD

Grouping with 'Include Other' automatically aggregates remaining items.

Why this answer

Option B is correct because grouping allows you to combine remaining values into an 'Other' group. Option A is wrong because a slicer does not aggregate. Option C is wrong because a calculated column would require manual grouping.

Option D is wrong because a filter would exclude products, not group them.

Practice this question →
240
MCQmedium

A company uses Row-Level Security (RLS) in Power BI. They want to ensure that when a manager views the report, they see data for their own region plus any region where a salesperson reports to them. Which RLS approach should you implement?

A.Use a DAX filter that references the USERPRINCIPALNAME() function
B.Use Power BI App permissions to restrict data
C.Create a static role for each manager and assign users
D.Apply RLS at the visual level using bookmarks
AnswerA

USERPRINCIPALNAME() can be used to look up the manager's data permissions dynamically.

Why this answer

Option A is correct because Row-Level Security (RLS) in Power BI uses DAX filters that can dynamically evaluate the current user's identity via USERPRINCIPALNAME() or USERNAME(). By creating a DAX rule that checks whether the manager's UPN matches the region manager or if the salesperson's manager UPN equals the current user, you can enforce dynamic, hierarchical data access without hardcoding roles per manager.

Exam trap

The trap here is that candidates often confuse RLS with app-level security or visual-level filtering, assuming that restricting access at the app or bookmark level can achieve row-level data isolation, but only DAX-based RLS can enforce dynamic, user-specific row filtering at the data source.

How to eliminate wrong answers

Option B is wrong because Power BI App permissions control access to the entire report or dashboard, not row-level data within a dataset; they cannot filter data by manager or region. Option C is wrong because creating a static role for each manager would require manual role creation and user assignment for every manager, which is not scalable and does not support dynamic hierarchy based on reporting structure. Option D is wrong because RLS cannot be applied at the visual level using bookmarks; bookmarks capture visual state (filters, slicers, selections) but do not enforce security—any user can bypass bookmarks by interacting with the report.

Practice this question →
241
MCQeasy

You need to create a measure that calculates the year-over-year growth percentage for sales. Which DAX function should you use?

A.DATEADD
B.PARALLELPERIOD
C.SAMEPERIODLASTYEAR
D.PREVIOUSYEAR
AnswerC

SAMEPERIODLASTYEAR returns the same period in the previous year, ideal for YoY.

Why this answer

The SAMEPERIODLASTYEAR function is the correct choice for calculating year-over-year growth because it shifts the current filter context back by one year, returning a set of dates exactly one year prior. This allows you to compute the prior year's sales and then derive the growth percentage using a formula like (Current Sales - Prior Year Sales) / Prior Year Sales.

Exam trap

The trap here is that candidates often confuse SAMEPERIODLASTYEAR with PREVIOUSYEAR, mistakenly thinking PREVIOUSYEAR can be used for any period comparison, but PREVIOUSYEAR only works for full calendar year comparisons, not for partial periods like months or quarters.

How to eliminate wrong answers

Option A is wrong because DATEADD shifts dates by a specified interval (e.g., -1 year) but returns a contiguous range of dates, which can cause unexpected results when the current period is not a full month or quarter. Option B is wrong because PARALLELPERIOD returns a parallel period of the same length in the previous period (e.g., previous month, quarter, or year) but shifts the entire period, which may not align with the exact same dates as the current period. Option D is wrong because PREVIOUSYEAR returns all dates in the previous calendar year, which is not suitable for year-over-year comparisons when the current period is not the entire year (e.g., comparing a single month or quarter).

Practice this question →
242
MCQhard

A company wants to create a Power BI report that shows sales performance by region. The data contains a table 'Sales' with columns: Date, Amount, RegionID, and ProductID. They also have a 'Regions' table with RegionID and RegionName. They want to display a matrix visual with RegionName on rows and Year on columns, with the sum of Amount as values. However, the report displays only 'RegionID' instead of 'RegionName'. What is the most likely cause?

A.The relationship is configured as many-to-many.
B.The relationship direction is set to Both.
C.The RegionID column in the Sales table is hidden.
D.There is no active relationship between the Sales and Regions tables.
AnswerD

Without an active relationship, Power BI cannot propagate filters from Sales to Regions, so it displays the foreign key column (RegionID) instead of RegionName.

Why this answer

Option D is correct because if there is no active relationship between the Sales and Regions tables, Power BI cannot use the RegionName from the Regions table to filter or group the Sales data. Instead, it defaults to displaying the RegionID from the Sales table, which is the only related field available in the visual. An active relationship must exist between the two tables on the RegionID columns for RegionName to appear in the matrix.

Exam trap

The trap here is that candidates often assume the RegionName column is missing due to a hidden column or relationship cardinality, but the core issue is the absence of an active relationship, which Power BI requires to combine data from different tables in a visual.

How to eliminate wrong answers

Option A is wrong because a many-to-many relationship would still allow RegionName to appear, though it might cause ambiguous aggregation; it does not cause the visual to show RegionID instead of RegionName. Option B is wrong because setting the relationship direction to Both (bidirectional cross-filtering) does not prevent RegionName from being used; it actually enables additional filtering but does not hide the RegionName column. Option C is wrong because hiding the RegionID column in the Sales table does not affect the display of RegionName from the Regions table; hiding a column only prevents it from appearing in the field list, not from being used in relationships or visuals.

Practice this question →
243
MCQhard

You have the above DAX measure. When you add it to a table visual with Year and Month, the values are correct for each month. However, when you add a slicer for Year and select 2023, the measure shows blank for all months. What is the most likely cause?

A.The measure uses DIVIDE instead of a simple division.
B.The Date table is not marked as a date table.
C.The Sales table has a many-to-many relationship with Date.
D.The slicer is filtering the Sales table directly.
AnswerB

Time intelligence functions require a date table to be marked as such.

Why this answer

The SAMEPERIODLASTYEAR function requires a continuous date range. If the Date table does not have a relationship to Sales or if the date table is missing dates (e.g., only dates where sales occurred), the function may return blank. However, a common issue is that the Date table must be marked as a date table.

Another cause could be that the date table does not contain dates for the previous year. But given the symptom 'shows blank for all months when slicer is applied', it suggests the date table is not continuous or not marked as date table. In Power BI, if a date table is not marked as a date table, time intelligence functions may not work correctly with slicers.

Practice this question →
← PreviousPage 4 of 4 · 243 questions total

Ready to test yourself?

Try a timed practice session using only Visualize and analyze the data questions.

Start 20-question session