BI Tools: Tableau, Power BI, and Looker Questions

Approach: Use the TopN slicer value from TopNParameter, rank products by Sales, return Sales for top N and aggregate the rest into 'Other'. Use SUMX over VALUES(Products) and a conditional check on RANKX.

DAX measure pattern:

TopN Sales with Other :=
VAR N = SELECTEDVALUE(TopNParameter[TopN], 5)
VAR ProdTable = VALUES(Products[ProductID])
VAR Ranked =
    ADDCOLUMNS(
        ProdTable,
        "Sales", CALCULATE(SUM(Orders[SalesAmount])),
        "Rank", RANKX(ALL(Products[ProductID]), CALCULATE(SUM(Orders[SalesAmount])), , DESC, DENSE)
    )
VAR TopNTable = FILTER(Ranked, [Rank] <= N)
VAR OtherTable = FILTER(Ranked, [Rank] > N)
RETURN
    IF(
        HASONEVALUE(Products[ProductID]),
        CALCULATE(SUM(Orders[SalesAmount])),
        SUMX(TopNTable, [Sales]) +
        IF(COUNTROWS(OtherTable)>0, SUMX(OtherTable,[Sales]),0)
    )

Key points: Use SELECTEDVALUE for slicer, RANKX over ALL to ignore product filter when ranking, ADDCOLUMNS to compute Sales once. This pattern preserves filter context for visuals while providing aggregated "Other" when needed.

Five report-design best practices for Power BI

1. Minimize visuals per page

• Why: Each visual issues queries; fewer visuals reduce query load and improve dataset performance.
• Tip: Limit to 6–8 visuals; prefer aggregated visuals.

2. Use aggregated tables and pre-aggregations

• Why: Shifts compute from runtime to ETL, lowers query complexity.
• Tip: Build aggregate tables in the warehouse and map them in the model.

3. Enforce a single date table (marked) and relationships

• Why: Ensures correct time-intelligence and efficient filtering.
• Tip: Create and mark a Date table in the model; avoid calculated date columns in visuals.

4. Avoid heavy row-level calculations in visuals

• Why: Row-level DAX or complex measures can be slow at scale.
• Tip: Push calculations to Power Query or upstream pipelines; use measures for aggregations only.

5. Accessibility and consistent naming

• Why: Improves usability and governance; consistent field names reduce dataset changes.
• Tip: Maintain a semantic layer: friendly field names, descriptions, and foldering; document in data catalog.

These practices reduce runtime pressure on pipelines and make maintenance predictable.

Filter propagation = how filters travel across relationships in the model and change the filter context for visuals and measures.

Behavior:

• Single-directional relationships propagate filters from the one side to the many side.
• Bidirectional allows filters to flow both ways, which can create broader filter contexts or ambiguity.

Concrete example:

• Date table [Date] (one) → Sales[SaleDate] (many), single-direction. When a visual filters Date = Jan 2025, that filter propagates to Sales and reduces rows used in [Total Sales].
• If you made the relationship bidirectional and linked Customer→Sales and Customer→Support, a filter on Support could unexpectedly filter Sales through Customer, changing totals.

As a data engineer I design clear star schemas and prefer single-direction Date→Fact to ensure predictable time-intel calculations and to avoid expensive bidirectional evaluation.

Problem: Calculated table in DAX joins large tables causing slow refresh.

Alternatives & trade-offs

1. Move join to ETL / warehouse

• Approach: create the joined table as a materialized table in ETL (Spark/dbt/SQL).
• Pros: leverages distributed compute, refresh incremental, reduces model memory.
• Cons: extra storage, longer ETL pipeline changes.

2. Use query folding in Power Query

• Approach: perform join in Power Query against source that supports folding (SQL, Synapse).
• Pros: pushes work to source, keeps model simpler.
• Cons: requires a foldable source and careful M steps; may not fold for complex transforms.

3. Change to Relationship-only tables

• Approach: keep base tables and create relationship-only lookup that uses existing keys; use DAX measures for queries.
• Pros: reduces stored model size, faster refresh.
• Cons: query-time joins can be slower for complex aggregations; need proper composite models.

4. Incremental calculated table or partitioning via aggregations

• Approach: precompute partitions or aggregations in ETL, use aggregations in model.

Recommendation: Prefer ETL/warehouse materialization for large joins. Use query folding if direct-write access exists. Reserve DAX calculated tables for small/derived sets. Measure impact by testing refresh time and model size.

Incremental refresh strategy

Parameters & partitioning

• In Power BI Desktop create two DateTime parameters: rangeStart and rangeEnd (DateTime).
• Apply a query filter on transaction date: Date >= rangeStart and Date < rangeEnd. Power BI will generate partitions per refresh policy.
• Partitioning strategy: daily partitions for the recent period (e.g., last 60 days) and monthly/weekly for older history to limit number of partitions while allowing efficient refresh.

Refresh policy

• Store period (how much historical data kept): e.g., 7 years.
• Refresh period (how much data is refreshed each run): e.g., last 7 days or last 1 day for daily freshness.
• Configure incremental refresh in table settings: set Detect data changes column if using CDC to optimize merges.

Back-end considerations

• Source is Azure SQL DB: ensure appropriate clustered index on date column, and partitioning strategy on source if possible to accelerate range queries.
• Use query folding: keep transformations that fold so only necessary date ranges are scanned.

Power BI Service

• Publish dataset to Premium or Pro with gateway for Azure SQL if in VNet. Configure scheduled refresh daily and on-demand for failures.

Testing & monitoring

• Validate in Desktop with sample ranges, then deploy to service and run incremental refresh manually to observe partition creation.
• Monitor refresh history in Power BI Service, and use Azure SQL Query Store to verify which queries run and duration. Add alerts for refresh failures or long-running queries.

This approach minimizes data movement by refreshing only recent partitions while keeping long-term history accessible and performant.