Skip to main content

Interview Questions

8. Interview Questions​

Q1: "What is the difference between a Calculated Column and a Measure?"​

Answer: "A Calculated Column is computed at data refresh time and stored row by row — like adding a Profit column (Revenue minus Cost). A Measure is computed at query time and changes dynamically based on the current filter context — like Total Revenue that updates when you select a region filter. I use measures for KPIs and metrics, and calculated columns only when I need a value per row for filtering or relationships."

Q2: "Explain CALCULATE with an example."​

Answer: "CALCULATE modifies the filter context of a calculation. For example, if I write SUM(Sales[Revenue]), it gives me total revenue based on whatever filters are active on the report. But CALCULATE(SUM(Sales[Revenue]), Products[Category] = 'Electronics') always gives me Electronics revenue regardless of what the user selects. It's the most powerful DAX function — used for year-over-year comparisons, percentage of total, and any scenario where I need to override or add filters."

Q3: "What is a Star Schema?"​

Answer: "A star schema organizes the data model into a central Fact table — which contains measurements like revenue and quantity — surrounded by Dimension tables that provide context — who bought it, what product, when, where. This structure is recommended by Microsoft for Power BI because DAX CALCULATE and filter propagation are optimized for it. The single-direction filtering from dimensions to facts keeps the model clean and performant."

Q4: "How do you handle slow Power BI reports?"​

Answer: "My optimization checklist: (1) Check the data model — are there unnecessary bidirectional relationships or many-to-many joins? Simplify to a clean star schema. (2) Reduce data volume — do I need 5 years of transactional data, or would aggregated data work? (3) Replace calculated columns with measures where possible — measures don't consume memory. (4) Minimize the number of visuals per page — each visual fires a separate query. (5) Use aggregation tables for large datasets. (6) Check DAX efficiency — avoid iterating over large tables when a simple aggregation would suffice."

Q5: "Difference between DirectQuery, Import, and Live Connection?"​

Answer: "Import loads data into Power BI's in-memory engine — fastest performance but data is only as fresh as the last refresh. DirectQuery sends queries to the source database in real-time — always current but slower because every interaction queries the database. Live Connection is specific to Analysis Services — similar to DirectQuery but the model lives on the server. I'd use Import for 95% of cases and set up scheduled refreshes."

Q6: "What is Filter Context vs Row Context?"​

Answer: "Filter Context is the set of active filters on a visualization — if a slicer is set to 'North Region', all measures calculate for North only. Row Context is the concept of iterating over individual rows — it exists inside calculated columns and iterator functions like SUMX. Understanding the difference is crucial for writing correct DAX. CALCULATE is the bridge — it can convert Row Context into Filter Context."

Q7: "How would you create a YoY comparison?"​

Answer: "I'd first ensure I have a proper Date Table with a continuous date range. Then use DAX time intelligence: the current year measure is SUM(Sales[Revenue]). For previous year, I'd use CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(DateTable[Date])). For growth percentage, I'd use DIVIDE to calculate (Current - Previous) / Previous × 100. This requires the Date Table to be marked as a date table in the model."

Q8: "What is Power Query and when do you use it?"​

Answer: "Power Query is the ETL layer in Power BI — I use it to clean and transform data before it loads into the model. Common operations include removing unnecessary columns, changing data types, splitting or merging columns, handling null values, unpivoting wide data into tall format, and merging tables using joins. All transformations are recorded as steps, so when data refreshes, the same cleaning process runs automatically."

Q9: "Explain Row Level Security."​

Answer: "RLS restricts data visibility based on the logged-in user. For example, a sales manager for the North region should only see North's data when they open the report. I define roles in Desktop with a DAX filter like [Region] = 'North', then assign specific users to that role in Power BI Service. The beauty is that one report serves all users — each just sees their authorized subset."

Q10: "Power BI vs Excel — when would you choose which?"​

Answer: "Excel for quick, ad-hoc analysis, small datasets under 100K rows, and when stakeholders need to edit data or do their own calculations. Power BI for larger datasets, interactive dashboards that need to be shared across a team, automated refresh from databases, and when you need features like drill-through, bookmarks, and RLS. In practice, many workflows start in Excel for exploration and graduate to Power BI for the production dashboard."