KPMG Recent Power BI Interview Q&A

1. What is a Factless Fact table?

A Factless Fact table is a fact table that does not have any measure or metric. It’s
useful when you want to track events or record transactions that happen over time,
but there is no numerical data to aggregate. For example, you may want to track
student attendance, but you don’t have any numeric data like scores or fees.

2. Can you give an example of Slowly Changing Dimensions?

Slowly Changing Dimensions (SCD) are dimensions in which data changes

slowly over time. For example, consider a customer's address—if a customer
moves to a new location, the new address needs to be recorded without losing the
history of the old one. In Power BI, you can handle this by maintaining a history of
the dimension using Type 2 SCD, which adds a new row to the table whenever a
change occurs.

3. What is Append, and what is the necessary condition to append queries?

Appending queries means combining two or more tables vertically. The

necessary condition for appending is that the tables should have the same
structure, meaning they should have the same number of columns and
corresponding data types.

4. What are Relationship Modifiers in Power BI?

Relationship modifiers in Power BI define how relationships behave between

tables. They include cross-filtering directions like single and both, and options like
making a relationship active or inactive. These modifiers control how data is
filtered and affect the calculations in your model.

5. What is Incremental Refresh, and will you actually implement it in your

model?
Incremental refresh allows you to refresh only a subset of your data instead of
reloading the entire dataset every time. Yes, I would implement it for large datasets
where refreshing the full data can be time-consuming. It saves processing time and
ensures that only new or updated data gets refreshed, which is essential in
environments with large historical datasets.

6. Can you tell me any use cases of using Bookmarks?

Bookmarks in Power BI can be used for storytelling and enhancing the

interactivity of reports. For example, you can create a bookmark to capture a
specific view of a report page (like a particular filter selection) and use it in
presentations or to allow users to toggle between different views without manually
applying filters again.

7. How can you differentiate between Related and Lookupvalue DAX

functions?

The Related function is used in one-to-many relationships to pull data from a

related table. It works when there’s an existing relationship between the tables. On
the other hand, Lookupvalue does not depend on relationships. You specify the
search conditions to retrieve data from any table, even if no formal relationship
exists between them.

8. What error do we get when we have many-to-many relationships between

two tables?

In Power BI, when you have many-to-many relationships, you may encounter
issues like ambiguity errors or inaccurate results. Power BI uses bidirectional
cross-filtering to manage this, but it requires careful handling to avoid performance
bottlenecks and logical errors in your model.

9. What are the limitations of using Direct Query connection mode reports?

One of the major limitations of Direct Query is that performance can suffer if the
underlying data source is slow, as every interaction in the report sends a query to
the database. Additionally, not all DAX functions are supported, and there are
limits on the size and complexity of the models you can build compared to Import
mode.

10. What is a Decomposition Tree visual, and how is it useful?

The Decomposition Tree visual is used for root cause analysis. It helps you break
down a measure (like sales) into its contributing factors (such as region, product, or
time). It is very useful when you want to explore hierarchical data and
understand the factors that contribute the most to a particular outcome.

11. How can you change the order of the values displayed on the X-axis of a
Column chart as per the requirement?

You can change the order of values on the X-axis by sorting the axis based on a
different column. For example, if you want to display months chronologically, you
can create a custom column with the month numbers and sort by that column rather
than the month name.

12. Can we implement RLS (Row Level Security) in Power BI?

Yes, Row Level Security (RLS) can be implemented in Power BI to restrict data
access for specific users. This is achieved by defining roles and security rules
within the Power BI model, ensuring users only see the data relevant to them. For
instance, sales managers should only view their region's data, not other regions.

13. What is the difference between calculated columns and measures?

A calculated column is computed during the data load and stored in the data
model, consuming more memory, while a measure is calculated dynamically based
on the report's context. Measures are usually more efficient for complex
calculations and are preferred when you want to minimize data storage overhead.

14. How do you handle performance optimization in Power BI?

There are several strategies to optimize performance in Power BI, such as using
aggregated tables, minimizing the use of calculated columns, avoiding
bi-directional relationships, and preferring measures over calculated columns.
Additionally, reducing data granularity and implementing Incremental Refresh
can help with optimization.

15. What is a surrogate key, and why do we use it in Power BI?

A surrogate key is an artificial key added to a table, usually in a dimension table,

to uniquely identify each row. It's typically used in situations where there is no
natural primary key or when handling slowly changing dimensions, allowing you
to manage historical data effectively.

16. How can you create a custom tooltip in Power BI?

You can create custom tooltips by designing a separate report page with the
specific details or visuals you want to show, then linking that page as a tooltip in
your visuals. This provides additional information or insights when users hover
over data points in the main report.

17. How do you handle large datasets in Power BI?

Handling large datasets in Power BI involves using Import mode for better
performance, applying Incremental Refresh, and reducing data at the source by
using SQL queries or filtering at the data load level. You also want to optimize
DAX queries and create aggregated views to ensure the report runs smoothly.

18. Can you explain what a KPI visual is and how it is used?

A KPI (Key Performance Indicator) visual in Power BI is used to show progress

toward a measurable goal. For example, it can track sales against targets. The KPI
visual uses indicators like color codes to quickly show whether the key metrics are
on track, ahead, or behind.

19. What is the role of Hierarchies in Power BI?

Hierarchies in Power BI allow you to group related data fields into a tree-like
structure, making it easier to drill down and explore data. For example, you can
create a hierarchy of Year → Quarter → Month in a date table, enabling users to
analyze trends at different time levels.
20. How can you implement drillthrough in Power BI reports?

Drillthrough enables users to right-click on a data point in one report and navigate
to a more detailed view in another page, with the context of the original data point
retained. It's useful when you want to let users explore details without cluttering
the main report page.