Power BI Questions interview question asked in ScatterPie Analytics 1st Round ( November 2024 )

My Power BI Journey: Answering #ScatterPie Analytics 1st Round

Today, I’m sharing my responses to a series of Power BI questions which has been asked in the #ScatterPie Analytics 1st Round interview. These questions not only tested my technical expertise but also explored how I approach problem-solving in real-world scenarios. Here's how I responded:

---

1. Walk Me Through Your Profile

I am a Sr. Data Analyst with over 3.5 years of experience specializing in data visualization and reporting. I have developed dynamic dashboards and reports in Power BI, leveraging my expertise in DAX, SQL, and data modeling. My work spans domains like sales, marketing, operations, and finance, delivering actionable insights that drive business decisions. I’ve also managed Row Level Security (RLS) implementations and optimized performance for datasets with millions of rows.

---

2. How Many Dashboards Have You Developed So Far?

I have developed 20+ dashboards, catering to diverse business needs. For instance, I’ve created interactive sales performance dashboards, real-time marketing ROI trackers, and operational efficiency reports. Each dashboard is tailored to deliver key insights efficiently and visually.

---

3. Describe the Project Development Process

a) Data Sourcing:

I typically source data from SQL databases, Excel files, APIs, and cloud platforms like Azure and Google Sheets.

b) Reading and Processing Data:

Using Power Query, I clean, transform, and load the data into Power BI, ensuring it’s optimized for analysis.

c) Gathering Requirements and Defining KPIs:

I collaborate closely with stakeholders to understand business needs, define KPIs, and map the data architecture accordingly.

---

4. What Is Your Role in the Workspace?

In my workspace, I:

• Manage datasets and reports.
• Create dashboards with interactive visuals.
• Implement Row Level Security (RLS) to ensure data integrity.
• Collaborate with team members for seamless report delivery.

---

5. What Is Row Level Security (RLS)? Provide Examples

RLS restricts data access based on user roles.

• Static RLS: Predefined roles, e.g., restricting access to sales data by department.
• Dynamic RLS: Uses DAX filters to apply conditions dynamically, e.g., showing sales only for the logged-in user’s region.

---

6. Does RLS Apply to a Member Role in the Workspace?

No, RLS does not work for users with Admin, Member, or Contributor roles in a workspace. RLS is effective only when users access reports through an app or shared link.

---

7. Why Do We Need a Master Calendar?

A master calendar ensures consistent time-based analysis across dimensions.

• Used with Dimensions: Enables filtering and grouping by dates.
• Not Used with Fact Tables: Fact tables focus on transactional data, and joining them with a calendar table can cause redundancy.

---

8. Difference Between Measure and Calculated Column

• Measure: Context-dependent, dynamic, and calculated on the fly.
• Calculated Column: Static, calculated row by row, and stored in the dataset.

---

9. Difference Between HAVING and WHERE Clause

• WHERE: Filters data before aggregation.
• HAVING: Filters data after aggregation.

Example:

sql

SELECT Category, SUM(Sales)  
FROM SalesData  
WHERE Region = 'East'  
GROUP BY Category  
HAVING SUM(Sales) > 5000;  

---

10. Measure vs. Calculated Column Output

When used in a table visual:

• Measure: Shows the dynamic sum of sales grouped by category.
• Calculated Column: Displays the same total sales value for every row.

---

11. DAX Formula for Sales of Category A Only

DAX

Measure = CALCULATE(SUM(Sales), Category = "A")  

---

12. Adding a City Column: Output Change

• Measure: Dynamically adjusts to show sales grouped by both category and city.
• Calculated Column: Repeats the same total sales value across all rows.

---

13. What Are Dataverse and Power Automate?

• Dataverse: A secure cloud-based platform for storing and managing data.
• Power Automate: A tool for automating workflows across apps and services.

---

14. Star Schema vs. Snowflake Schema

• Star Schema: Direct relationships, simpler design, better for smaller datasets.
• Snowflake Schema: Normalized tables, ideal for complex datasets requiring higher consistency.

---

15. Techniques to Reduce Load in Power BI

• Import only necessary columns.
• Filter data at the source using SQL queries.
• Use incremental refresh and aggregations for large datasets.

---

16. Challenges I’ve Faced in Projects

One challenge was optimizing query performance for a dashboard handling 10 million+ rows of sales data. I resolved this by:

• Creating aggregate tables.
• Implementing incremental refresh to reduce load times.

---

17. Do You Have Any Questions?

I asked:

“What are the immediate challenges this team is facing, and how can I contribute to overcoming them?”