View Full ServiceNow CIS-ITSM Exam Dumps and Practice Test Dumps.

Q1. You are preparing a Power BI model and need to combine customer data from two tables by matching CustomerID values. Which Power Query operation should you use?

1. Append Queries
2. Merge Queries
3. Group By
4. Unpivot Columns

Correct Answer: 2. Merge Queries

Explanation: Merge Queries combines tables horizontally by matching rows based on one or more common key columns, such as CustomerID. This operation is similar to a database join and allows you to bring related columns from one table into another. Append Queries is used when you want to stack rows from tables with similar structures. Group By summarizes rows based on selected fields, while Unpivot Columns converts columns into attribute-value row pairs. When customer details need to be matched with another table using a shared identifier, Merge Queries is the appropriate Power Query transformation.

Q2. You need to create a measure that calculates the average value of the SalesAmount column. Which DAX function should you use?

1. SUM
2. COUNT
3. AVERAGE
4. DISTINCTCOUNT

Correct Answer: 3. AVERAGE

Explanation: The AVERAGE function returns the arithmetic mean of numeric values in a column. When used in a measure, it evaluates the SalesAmount values within the current filter context, so report filters and slicers can dynamically change the result. SUM would return the total sales amount, COUNT would count nonblank values, and DISTINCTCOUNT would count unique SalesAmount values. AVERAGE is most suitable when the business requirement is to determine the typical or mean sales value across the currently selected transactions. Measures using AVERAGE can be reused across cards, charts, tables, and other report visuals.

Q3. Your Power BI model contains a Date table and a Sales table. What should you do to support reliable time-intelligence calculations?

1. Create an active relationship between the Date table and the Sales date column.
2. Remove the Date table and use text-based month names.
3. Convert all date values to decimal numbers.
4. Use only calculated columns in the Sales table.

Correct Answer: 1. Create an active relationship between the Date table and the Sales date column.

Explanation: Time-intelligence calculations work most effectively when the model contains a dedicated Date table that is related to the fact table through a date column. The relationship allows date filters to propagate correctly to the Sales table and supports calculations such as year-to-date, previous year, and month-to-date values. Using text month names does not provide a complete chronological date structure. Converting dates to decimals is unnecessary and would make analysis more difficult. A properly configured Date table improves consistency across reports and is considered a standard modeling practice in Power BI.

Q4. A report page contains a bar chart that shows sales by product. You want users to see additional product details when they hover over a bar. Which feature should you use?

1. Drillthrough
2. Bookmark navigator
3. Report tooltip
4. Row-level security

Correct Answer: 3. Report tooltip

Explanation: Report tooltips allow you to display additional contextual information when a user hovers over a data point in a visual. A tooltip page can contain multiple visuals, measures, and descriptive information that are automatically filtered based on the selected data point. Drillthrough sends users to another report page for deeper analysis, while bookmarks capture report states and support navigation. Row-level security controls which rows users are permitted to see. If the goal is to show more information without requiring users to leave the current page, a report tooltip is the most appropriate feature.

Q5. You need a DAX measure that calculates the total number of unique customers who made purchases. Which function should you use?

1. COUNT
2. COUNTROWS
3. SUM
4. DISTINCTCOUNT

Correct Answer: 4. DISTINCTCOUNT

Explanation: DISTINCTCOUNT counts the number of unique values in a specified column. If the Sales table contains multiple transactions for the same customer, using COUNT would count each nonblank CustomerID occurrence and could overstate the number of customers. COUNTROWS would count rows in the table rather than unique customers, while SUM is intended for numeric aggregation. DISTINCTCOUNT is appropriate for metrics such as unique customers, unique products sold, or unique orders when the relevant identifier may appear multiple times in the fact table. The measure also responds automatically to the current filter context.

Q6. A text column contains values such as “North – Retail”. You need separate Region and Channel columns. Which Power Query transformation is most appropriate?

1. Split Column
2. Pivot Column
3. Group By
4. Replace Errors

Correct Answer: 1. Split Column

Explanation: Split Column divides a single column into multiple columns based on a delimiter, character position, or other splitting rule. In this scenario, the hyphen can be used as the delimiter to separate “North” from “Retail”, creating distinct Region and Channel columns. Pivot Column restructures rows into columns, while Group By aggregates rows based on selected fields. Replace Errors is used for handling error values. Splitting combined attributes during the Power Query stage improves model structure and makes it easier to use each attribute independently in slicers, filters, relationships, and visualizations.

Q7. You want to calculate sales year-to-date using DAX. Which function is most directly suited to this requirement?

1. VALUES
2. TOTALYTD
3. RANKX
4. LOOKUPVALUE

Correct Answer: 2. TOTALYTD

Explanation: TOTALYTD evaluates an expression from the beginning of the year through the current date in the filter context. It is commonly used for financial and operational reporting when users need cumulative values within a year. The model should contain a properly configured Date table to support reliable time-intelligence calculations. VALUES returns distinct values from a column or table, RANKX ranks values based on an expression, and LOOKUPVALUE retrieves a value based on matching criteria. TOTALYTD is specifically designed to simplify year-to-date calculations and responds dynamically when users filter by dates or other report dimensions.

Q8. You have a dimension table in which ProductID contains duplicate values. You want to use ProductID on the one side of a one-to-many relationship. What should you do first?

1. Add more duplicate ProductID values.
2. Change the relationship to many-to-many immediately.
3. Ensure ProductID contains unique values.
4. Hide ProductID from the report.

Correct Answer: 3. Ensure ProductID contains unique values.

Explanation: The column on the one side of a one-to-many relationship must contain unique values. A Product dimension should normally have one row per product, with ProductID serving as the unique key. If duplicate ProductID values exist, the table should be cleaned or redesigned before the relationship is created. Changing the relationship to many-to-many simply to accommodate duplicates may introduce unnecessary complexity and ambiguous filtering behavior. Hiding the field does not change its uniqueness. Ensuring a clean dimension table supports a clear star schema and helps DAX calculations produce predictable results.

Q9. A report requires a single value showing the total revenue for the current filter context. Which visual is usually most appropriate?

1. Card
2. Scatter chart
3. Treemap
4. Matrix

Correct Answer: 1. Card

Explanation: A Card visual is designed to prominently display a single summarized value, such as total revenue, total customers, profit margin, or number of orders. It works well for key performance indicators that users need to understand quickly. A scatter chart is used for analyzing relationships between numerical variables, while a treemap shows proportional values across categories. A matrix is more suitable for detailed tabular analysis with rows and columns. When the requirement is to emphasize one important metric based on the current report filters, a Card provides a clear and efficient presentation.

Q10. You need to stop an imported table from being loaded into the Power BI model while still using it as an intermediate query for other transformations. What should you do?

1. Delete the query.
2. Disable load for the query.
3. Convert the query to a visual.
4. Create a measure from the query.

Correct Answer: 2. Disable load for the query.

Explanation: Disabling load allows a Power Query query to remain available as a staging or intermediate query without loading its rows into the semantic model. This is useful when one query is referenced by other queries but does not need to appear as a table in the final model. Deleting the query could break dependent transformations. A query cannot simply be converted into a visual, and creating a measure does not address data-loading behavior. Using staging queries with load disabled can help organize transformation logic and may reduce unnecessary model size when intermediate datasets are not required for reporting.

Q11. A company wants users to compare actual sales against a sales target on a single visual. Which visual is suitable for showing progress toward a target?

1. Pie chart
2. Table
3. Gauge
4. Ribbon chart

Correct Answer: 3. Gauge

Explanation: A Gauge visual is designed to display a current value relative to a target or defined range. It can help users quickly assess whether a performance metric such as sales, profit, or service volume is below, near, or above the intended target. A pie chart shows proportions, while a table presents detailed values without emphasizing goal progress. A ribbon chart is useful for showing changes in category ranking over time. Although other KPI-oriented visuals may also be used depending on the design, a Gauge is a straightforward choice when the requirement is to display progress toward a specific target.

Q12. You need a DAX calculation to evaluate an expression under a modified filter context. Which function should you use?

1. CALCULATE
2. CONCATENATE
3. ROUND
4. FIND

Correct Answer: 1. CALCULATE

Explanation: CALCULATE is one of the most important DAX functions because it evaluates an expression after modifying the current filter context. It can add, replace, or remove filters and is commonly used for filtered totals, ratios, time-intelligence calculations, and comparisons. CONCATENATE joins text strings, ROUND changes the number of decimal places in a numeric value, and FIND locates one text string inside another. Understanding CALCULATE is fundamental to creating advanced Power BI measures because it provides precise control over how filters influence an expression and allows calculations to adapt dynamically to different report contexts.

Q13. You want a slicer selection for Year to remain consistent when a user navigates between multiple report pages. Which feature should you configure?

1. Drill mode
2. Sync slicers
3. Data alerts
4. Query parameters

Correct Answer: 2. Sync slicers

Explanation: Sync slicers allows a slicer selection to remain synchronized across selected report pages. For example, if a user selects 2026 on one page, the same Year filter can continue to apply when the user navigates to another synchronized page. Report designers can also decide whether the slicer is visible on each page. Drill mode is used to navigate hierarchy levels within visuals, while data alerts monitor supported values. Query parameters affect data retrieval or transformation rather than cross-page report interactions. Sync slicers is therefore the correct feature for maintaining consistent filtering across report pages.

Q14. You need to show individual sales transactions while allowing users to expand rows by Region and Product hierarchy. Which visual is most suitable?

1. Card
2. Donut chart
3. Gauge
4. Matrix

Correct Answer: 4. Matrix

Explanation: A Matrix visual supports hierarchical rows and columns, expandable and collapsible groups, subtotals, and detailed values. It is particularly suitable when users need to analyze data at multiple levels, such as Region, Product Category, and Product. A Card displays only a single summarized value. A Donut chart shows proportional category values and is not designed for detailed hierarchical exploration. A Gauge focuses on progress toward a target. The Matrix is therefore the strongest choice when the requirement includes structured tabular analysis combined with drillable or expandable hierarchy levels.

Q15. A column contains errors caused by invalid numeric conversions. You want to replace all errors with zero in Power Query. Which transformation should you use?

1. Replace Errors
2. Remove Duplicates
3. Sort Ascending
4. Fill Down

Correct Answer: 1. Replace Errors

Explanation: Replace Errors allows you to substitute error values in a Power Query column with a specified replacement value, such as zero. This can be helpful when invalid source values or failed conversions would otherwise prevent clean analysis. Remove Duplicates eliminates repeated rows or values, Sort Ascending changes row ordering, and Fill Down propagates the previous nonblank value into lower blank rows. Although it is also important to investigate why errors occurred, Replace Errors is the appropriate transformation when the business rule specifies that these error values should be converted to a known default such as zero.

Q16. You need users to access different detailed report pages from buttons without changing the underlying data. Which feature can store report states and support navigation actions?

1. Incremental refresh
2. Query folding
3. Bookmarks
4. Relationship cardinality

Correct Answer: 3. Bookmarks

Explanation: Bookmarks capture the current state of a report page, including filters, slicers, visibility settings, and other display properties. They can be associated with buttons and navigators to create custom navigation experiences, menus, or alternate report views. Incremental refresh controls how partitions of data are refreshed. Query folding relates to pushing Power Query transformations back to the source system, while relationship cardinality determines how model tables relate to one another. When the goal is to provide interactive report navigation without modifying the underlying data, bookmarks are an appropriate design feature.

Q17. You have a very large DirectQuery model and want to improve report performance. Which approach can help reduce the number of detailed queries sent to the source?

1. Add unnecessary calculated columns.
2. Use aggregation tables where appropriate.
3. Convert all numeric fields to text.
4. Add more bidirectional relationships.

Correct Answer: 2. Use aggregation tables where appropriate.

Explanation: Aggregation tables can improve performance by storing summarized data that Power BI can use for higher-level queries instead of repeatedly sending detailed queries to a large source. This can be especially useful in DirectQuery and composite-model scenarios. Unnecessary calculated columns can increase model complexity and resource usage. Converting numeric fields to text can reduce analytical flexibility and may increase storage requirements. Additional bidirectional relationships can create ambiguous filtering and performance issues. Well-designed aggregations, appropriate source optimization, efficient measures, and carefully planned relationships can significantly improve responsiveness in large Power BI models.

Q18. You want a visual to display the top five products based on total sales. Which Power BI feature can you apply directly to the visual?

1. Top N filter
2. Row-level security
3. Bookmark
4. Data source credential

Correct Answer: 1. Top N filter

Explanation: A Top N filter allows a report designer to limit a visual to the highest or lowest number of categories based on a selected measure. For example, you can configure a Product filter to show the top five products by Total Sales. Row-level security restricts data access for users rather than ranking visual categories. Bookmarks capture report states, while data source credentials control authentication to underlying data. Top N filtering is useful for focusing attention on leading or underperforming items and helps reduce visual clutter when the underlying category contains many values.

Q19. You create a new calculated column in a large fact table, but the same result could instead be calculated dynamically with a measure. Which option generally uses less model storage?

1. Calculated table
2. Duplicate query
3. Measure
4. Calculated column

Correct Answer: 3. Measure

Explanation: Measures generally require less model storage than calculated columns because measures are evaluated dynamically when a visual or query requests the result. Calculated columns are computed for every row and stored in the semantic model, which can significantly increase memory usage in a large fact table. Measures are especially appropriate for aggregations, ratios, percentages, and other calculations that need to respond to filter context. Calculated columns remain useful when a row-level value must be stored and used for grouping or relationships, but they should not be created unnecessarily when a measure can satisfy the reporting requirement.

Q20. You publish a Power BI semantic model that must refresh from an on-premises SQL Server data source. What component is typically required to enable scheduled refresh in the Power BI service?

1. Power BI mobile app
2. Report tooltip page
3. Power BI bookmark
4. On-premises data gateway

Correct Answer: 4. On-premises data gateway

Explanation: An on-premises data gateway provides a secure connection between the Power BI service and supported data sources located within an organization’s local network. It allows scheduled refresh and other supported connectivity scenarios without requiring the on-premises database to be exposed directly to the public internet. The gateway must be installed, configured, and associated with the correct data source and credentials. The Power BI mobile app is used for viewing content, while tooltip pages and bookmarks are report-design features. Therefore, the on-premises data gateway is typically required when refreshing cloud-hosted Power BI content from an on-premises SQL Server source.