Power BI Fundamentals and Microsoft Ecosystem Questions

Fundamentals of Power BI usage, including Power BI Desktop and Power BI Service, data modeling with DAX, report and dashboard design, data connectivity within the Microsoft ecosystem (Excel, SQL Server, Azure Synapse/Azure SQL Database, Azure Data Lake), and governance, security, deployment patterns, and best practices for BI solutions in Microsoft-centric environments

MediumTechnical

27 practiced

Explain how to configure Incremental Refresh in Power BI Desktop for a large table that receives daily append-only loads. Include how RangeStart and RangeEnd parameters are used, why query folding is essential, how partitions are created in the Service, local testing tips, and how to monitor partition refresh progress in Power BI Service.

Sample Answer

Start by creating two Date/DateTime parameters in Power BI Desktop: RangeStart and RangeEnd. Use them in the table query to filter the date column so the query engine can push the filter to the source (query folding).

Example Power Query (M):

// Parameters: RangeStart (datetime), RangeEnd (datetime)
Source = Sql.Database("server","db", [Query="SELECT * FROM dbo.Events WHERE EventDate >= @RangeStart AND EventDate < @RangeEnd"]),
// Or when using UI filtering:
FilteredRows = Table.SelectRows(SourceTable, each [EventDate] >= RangeStart and [EventDate] < RangeEnd)

Why query folding matters- Incremental refresh relies on the engine translating the RangeStart/RangeEnd filter into source-side predicates. If folding breaks (custom steps, client-side transformations), Power BI will retrieve entire table and incremental policy cannot be applied.

Configure incremental refresh in Desktop1. Right-click table → Incremental refresh.2. Define policy: how many years/months to store and how many days to refresh (for append-only daily loads, e.g., store 5 years, refresh last 5 days).3. Enable "Detect data changes" only if rows can be updated; for append-only, not required.

How partitions are created in Service- When published, Power BI Service automatically creates partitions per policy (typically one partition per refresh range slice, e.g., daily). For append-only daily loads set a small refresh window (e.g., last 1–7 days) so only recent partitions get refreshed.

Local testing tips- Set RangeStart/RangeEnd to a small test window (single day) and use “RangeStart = DateTime.AddDays(DateTime.LocalNow(), -1)” etc.- Use View Native Query in Power Query to verify folding.- Use “Refresh Preview” and test parameter values to ensure source returns only filtered data.

Monitoring partition refresh in Power BI Service- In workspace → Datasets → Settings → Incremental refresh you can view policy; use Refresh History (right-click dataset → Refresh history) to see which partitions refreshed.- For Premium workspaces, use XMLA endpoints or REST APIs to list partitions and check partition refresh progress and status.- In Premium, the Admin Monitoring or Premium Capacity metrics app shows refresh resource usage and durations.- Also check dataset refresh details in the new dataset refresh UI for per-partition timing/info.

Key best practices- Keep transformations foldable; push joins/filters to source.- Use a narrow refresh window for append-only loads.- Test folding and parameter behavior locally before publishing.- Use Premium XMLA or REST APIs for advanced partition management/monitoring.

MediumTechnical

20 practiced

When is it appropriate to build or use a custom visual for Power BI (using the Power BI visuals SDK) versus using built-in visuals or certified marketplace visuals? Describe a checklist to decide, and steps you would take to validate performance, security, and accessibility if using custom or marketplace visuals.

Sample Answer

Use a custom visual when built-in or certified marketplace visuals cannot meet a clear business need (unique interaction, visualization type, brand requirements, or heavy customization/performance tuning). Prefer built-ins or certified marketplace visuals when they meet requirements — they’re faster to deploy, maintained, and vetted for security/accessibility.

Checklist to decide:- Requirements fit: Can built-ins/marketplace achieve the needed chart, interaction, or layout?- Time vs ROI: Is development/maintenance cost justified?- Reuse scope: Will multiple reports/users benefit?- Maintenance & ownership: Who will update for Power BI changes?- Compliance: Does org policy forbid uncertified visuals?- Performance constraints: Will visual handle expected data volume?- Accessibility & localization needs

Validation steps for custom or marketplace visuals:

Performance- Benchmark with realistic datasets (row counts, fields, slicer combos)- Measure render time, interactivity latency, memory usage in Desktop and Service- Test in worst-case scenarios (many visuals on page, slow network)- Use profiling tools (browser dev tools) and iterative optimization (throttling, virtualization)

Security- Source provenance: prefer certified marketplace or open-source with review- Scan code for XSS, eval/use of third-party libs, insecure endpoints- Review permissions and data exfiltration risks (no unexpected network calls)- Conduct code review and request vendor security docs / SOC reports for marketplace visuals

Accessibility- Verify keyboard navigation, focus order, and screen-reader labels (ARIA)- Ensure color contrast and alternatives for colorblind users- Test with Narrator/VoiceOver and keyboard-only flows- Provide accessible descriptions and fallback data tables

Deployment checklist- Pilot in a controlled workspace with stakeholders- Monitor telemetry for errors/performance after rollout- Document known limitations and fallback options- Plan post-deployment updates and testing when Power BI updates

This approach balances speed and safety: use built-ins/certified visuals when possible; build or adopt custom visuals only when necessary and validate thoroughly across performance, security, and accessibility before wide release.

EasyTechnical

26 practiced

What is a star schema in dimensional modeling and why is it recommended for Power BI datasets? Explain the roles of fact and dimension tables, benefits for query performance and DAX calculations, how it supports time intelligence, and common anti-patterns to avoid such as over-normalized snowflake schemas or giant wide fact tables.

EasyTechnical

43 practiced

Explain the difference between calculated columns and measures in Power BI (DAX). Discuss where each is stored, when the calculation is performed (import-time versus query-time), effects on memory and model size, interactions with row and filter context, and provide one example where a calculated column is appropriate and one where a measure is preferable.

MediumTechnical

25 practiced

How would you model and handle many-to-many relationships in Power BI without sacrificing correctness or performance? Discuss options such as using a bridge table (junction table), the built-in many-to-many cardinality, and bi-directional filtering. For each option outline pros and cons, and recommend best practices for DAX measures in these scenarios.

Sample Answer

Start by clarifying correctness vs performance: correctness = expected row-level semantics; performance = model simplicity and query speed. There are three common options.

1) Bridge (junction) table (recommended default)- Pattern: normalize many-to-many into two one-to-many relationships via a bridge table (e.g., Products ← ProductCategoryBridge → Categories).- Pros: explicit lineage, predictable filter flow, fine-grained control, works well with star schemas and large models.- Cons: extra table and joins; need to maintain bridge keys and cardinality.- DAX tip: write measures using explicit filter/context functions to avoid implicit cross-filtering surprises. Example: SalesByCategory := CALCULATE( SUM(Sales[Amount]), FILTER(VALUES(ProductCategoryBridge[CategoryID]), COUNTROWS( FILTER(RELATEDTABLE(Products), Products[ProductID] = ProductCategoryBridge[ProductID]) )>0))

2) Built-in many-to-many (composite models / DirectQuery storage relationships)- Pattern: create a direct many-to-many cardinality between tables.- Pros: simpler model, less ETL.- Cons: can be ambiguous for filter propagation; performance may degrade for large cardinalities; harder to debug.- DAX tip: avoid relying on implicit behavior; use TREATAS or CROSSFILTER to control propagation.

3) Bi-directional filtering- Pattern: enable both directions on relationships.- Pros: convenient for slicers and natural reporting (filters flow both ways).- Cons: can create ambiguous relationships, slow queries, and incorrect totals if multiple bidirectional paths exist.- DAX tip: prefer one-directional relationships and use CROSSFILTER or USERELATIONSHIP locally in CALCULATE when you need reverse flow. Example safe pattern: TotalSales_Filtered := CALCULATE( SUM(Sales[Amount]), CROSSFILTER(Products[ProductID], Bridge[ProductID], Both), Bridge[CategoryID]=SELECTEDVALUE(Categories[CategoryID]) )

Best practices- Prefer bridge tables for correctness and scale; use many-to-many cardinality only for small lookup sets or prototypes.- Keep relationships single-direction by default; enable bi-directional only on isolated, well-understood paths.- Use DAX functions (TREATAS, CROSSFILTER, USERELATIONSHIP, ALL/REMOVEFILTERS) to make filter intent explicit.- Test measure correctness with small datasets and use Performance Analyzer; materialize intermediate tables (calculated tables/indexes) if queries are heavy.- Document model choices so report consumers understand behavior.

Unlock Full Question Bank

Get access to hundreds of Power BI Fundamentals and Microsoft Ecosystem interview questions and detailed answers.

Join thousands of developers preparing for their dream job.