BI Tools: Tableau, Power BI, and Looker Questions

Parameters let analysts add interactivity and model assumptions without changing source data. Below are advanced uses for Tableau, Power BI, and Looker with practical examples and performance considerations.

Tableau — Parameter Actions

• Uses: scenario modeling (e.g., change discount %, margin assumptions), dynamic binning (user chooses bin width or number of bins), switching measures (single axis that swaps between Revenue, Profit, Volume).
• Example: create parameter “Discount %” and use it in calculated field NewPrice = [List Price]*(1-[Discount %]); expose via parameter action so clicking a mark sets the parameter for rapid what-if comparisons across charts.
• Notes: Parameter actions are client-side and fast; but complex parameter-driven calculations repeated across many LODs or table calculations can increase compute and dashboard load time.

Power BI — What-if Parameters

• Uses: add slicer-driven numerical inputs for sensitivity analysis, scenario toggles, and incremental forecasts.
• Example: create What-if parameter “Ad Spend Increase” to generate alternate revenue forecasts in DAX measures. Combine with bookmarks or sync slicers to switch scenarios.
• Notes: What-if parameters create a disconnected table and are evaluated by DAX. Heavy use (many granular what-if tables or row-level iterators) can slow visual rendering and increase memory usage; prefer simple measures and aggregations pushed to the data model.

Looker — User Attributes

• Uses: personalize content (default date ranges, currency, or region), control access to content, dynamic filters for PDTs/Explores, and switching measures via liquid/templated filters.
• Example: set user_attribute country to limit Explore results and to parameterize an SQL-derived measure for local currency conversion. Use liquid to inject attribute into a CASE statement that selects a measure to display.
• Notes: User attributes evaluate server-side; if used inside derived tables or PDTs they can multiply PDT variants and increase build time. Avoid embedding attributes into large, frequently refreshed SQL that forces multiple compilations.

Performance implications and best practices

• Minimize parameter-driven row-level calculations in the visualization layer; push heavy computation into the data model or pre-aggregations.
• Cache-friendly patterns: use parameters to toggle precomputed measures rather than re-evaluating complex logic on every interaction.
• Limit the number of parameters and the cardinality of what-if tables; prefer sliders with coarse steps for broad sensitivity and sample-based drill-down for finer analysis.
• Test with representative dataset sizes, use performance recording/profiler tools (Tableau’s Performance Recorder, Power BI Performance Analyzer, Looker Query Inspector), and document parameter effects for stakeholders.

These patterns let BI analysts enable rapid exploration (scenario modeling, dynamic binning, measure switching) while maintaining performant, maintainable dashboards.

1. Clarify scope & reproduce

• Ask: same user region/browser? Interactive vs initial load? Scheduled extract refreshes running at load time?
• Reproduce: open dashboard with Performance Recorder in Tableau Desktop and on Tableau Server (or use Server Resource Monitoring / VizQL logs).

2. Identify bottleneck (DB vs client vs server)

• DB-bound signs: long query times in Performance Recorder / VizQL (Query time >> Rendering time), slow SQL in database profiler, CPU/waits on DB host.
• Client/server-bound signs: long “Rendering” or “Processing” times, many marks, heavy JavaScript/CSS, VizQL worker high CPU, network latency high but DB queries fast.
• Tools: Tableau Performance Recorder (shows Query, Compute, Rendering, Client Time), Tableau Server Admin Views, DB slow query logs, network trace.

3. Deep-dive diagnostics

• Capture performance recording for cold and warm loads (first vs repeat).
• Inspect top queries: check full-table scans, missing indexes, large sorts, cross-database joins, high row counts returned.
• Check number of marks, complex table calculations, LODs, nested quick filters, and dashboard actions causing full reloads.

4. Optimization plan (database)

• Create aggregated tables or materialized views matching dashboard grain.
• Add/selective indexes; avoid SELECT *; push computations to DB if set-based and more efficient.
• Replace expensive cross-DB joins with pre-joined extracts or consolidated schema.
• Consider read replica for analytics or query warehouses (Redshift, BigQuery, Snowflake).

5. Optimization plan (Tableau)

• Use extracts (Hyper) rather than live if queries are heavy and data freshness tolerates it. Use incremental refresh to limit extract size.
• Apply extract filters (date ranges, relevant dimensions) and hide unused fields to reduce extract footprint.
• Use context filters to limit data scanned by dependent quick filters (set only 1 context filter).
• Reduce quick filters: replace many single-value quick filters with parameter controls, cascading filters, or a single filter with search.
• Limit marks: use aggregated charts (don’t plot row-level if not needed), pagination for tables, use BINs or Top N.
• Simplify calculations: precompute heavy table calcs/LOD in DB or extract; avoid row-level calculated fields that force row scan.
• Minimize dashboard objects and sheets: consolidate multiple similar sheets with parameter-driven views; use tiled layout to reduce redraws.
• Disable “Show all values” for filters that cause large queries; set filter to only relevant values.
• Tune workbook: remove unused fields, reduce custom SQL, avoid multiple data sources when possible, use published data sources.
• Use Tableau Server caching settings and schedule extracts off-peak; enable Query Caching where safe.

6. Quick wins (apply immediately)

• Turn high-cardinality quick filters into parameters or search boxes.
• Convert heavy live connection to an extract for the dashboard only.
• Hide unused fields and publish a cleaned, published data source.
• Make the default view use a short date window (last 30/90 days) so initial queries are small.
• Replace table visual showing millions of rows with aggregated summary plus drill-through.
• Precompute common aggregates/materialized views.

7. Measure & iterate

• After each change, re-run Performance Recorder and compare times. Prioritize changes with highest time reductions for lowest effort.
• Document changes, schedule extract refreshes, and add monitoring (alerts on query time or server CPU).

Trade-offs

• Extracts improve speed but add freshness lag and storage.
• Context filters speed dependent filters but add maintenance complexity.
• Pre-aggregations reduce flexibility (less ad-hoc slicing).

This systematic approach finds where time is spent, applies targeted DB or Tableau fixes, and uses quick wins to reduce the 90s load toward sub-10s interactive performance.

ALLEXCEPT removes all filters from a table except those on specified columns. In DAX it's useful when you want to compute an aggregate at a particular granularity (e.g., Category) while ignoring slicers/filters on every other column (e.g., Product). Contrast with:

• ALL(Table or Column): removes all filters on the specified table/column(s) completely.
• ALLSELECTED(Table or Column): removes filters but keeps outer/user selection context (useful for respecting slicer selections across visuals).

Example: compute Category total sales that ignore Product-level selection but still respect Category filter.

CategoryTotalSales :=
CALCULATE(
  SUM(Sales[SalesAmount]),
  ALLEXCEPT(Product, Product[Category])
)

Explanation:

• If a user selects specific products, ALLEXCEPT(Product, Product[Category]) clears product and other Product-table filters but preserves any filter on Product[Category]. So CALCULATE sums SalesAmount across all products in the currently filtered category.
• Using ALL(Product) would ignore Category too (giving grand total); ALLSELECTED(Product) would behave differently when visual-level/context selections exist.

Use cases: category-level KPIs on visuals where product slicers should not change category totals. Watch out for relationships: ensure Product is properly related to Sales and consider additional filters from other tables (you may need to combine ALLEXCEPT with REMOVEFILTERS/KEEPFILTERS depending on desired behavior).

Approach (brief): implement a small DAX measure that returns a hex color (or named color) based on revenue vs target and a variance parameter. Use that measure in the visual’s conditional formatting (Format by > Field value). For large tables consider performance trade-offs: measures are evaluated in context per cell, so prefer simple arithmetic, minimize context transitions, and use calculated columns when values are static.

DAX measure example:

RevenueColor =
VAR Rev = SUM('Fact'[Revenue])
VAR Tgt = SUM('Fact'[Target])
VAR VarPct = SELECTEDVALUE('Params'[Variance], 0.05)  -- e.g. 0.05 = 5%
VAR Lower = Tgt * (1 - VarPct)
VAR Upper = Tgt * (1 + VarPct)
RETURN
SWITCH(
  TRUE(),
  ISBLANK(Rev) || ISBLANK(Tgt), "#FFFFFF",          -- white for missing data
  Rev < Lower, "#FF4C4C",                           -- red
  Rev <= Upper && Rev >= Lower, "#FFD766",          -- yellow
  Rev > Upper, "#4CAF50",                           -- green
  "#FFFFFF"
)

How to apply:

• Add the measure to the model.
• In the table/matrix visual select the Revenue column → Conditional formatting → Background color (or Font color) → Format by: Field value → Choose RevenueColor measure.

Performance considerations:

• Per-row measures run for every visual cell; aggregated SUMs are cheap but expensive calculations (e.g., LOOKUPVALUE, FILTER over large tables) will slow rendering.
• If variance is constant per row (not user-driven), compute a calculated column or precompute color in ETL to avoid repeated evaluation.
• If variance is a slicer/what-if parameter, caching helps but changing slicer forces recompute for visible cells.
• Use variables, avoid row-by-row iterators (e.g., FILTER on large tables), and keep logic in native aggregations.
• For very large tables, prefer conditional formatting rules (built-in Rules dialog) on aggregated measures where possible, or use color scales instead of per-row field-value measures.

This balances correctness, maintainability, and responsiveness for interactive reports.

To calculate Total Sales in a single base currency, handling nulls and using exchange rates, you can create a measure like this:

Total Sales (Base) =
SUMX(
    Sales,
    COALESCE( Sales[Amount], 0 )                // treat null amounts as 0
    * COALESCE( RELATED( ExchangeRate[Rate] ), 1 )  // convert using related rate; default 1 if missing
)

Explanation (plain language):

• SUMX(Sales, ...): iterates row-by-row over the Sales table and sums the expression for each sale. Use SUMX because we need to convert each row's amount before aggregating.
• COALESCE(Sales[Amount], 0): replaces null/blank Amount values with 0 so they don't break the sum.
• RELATED(ExchangeRate[Rate]): pulls the matching exchange rate from the ExchangeRate table; this assumes a relationship exists from Sales[Currency] → ExchangeRate[Currency].
• COALESCE(..., 1): if no rate is found, default to 1 to avoid null multiplication (you may prefer to return BLANK() or flag missing rates depending on business rules).

Alternative if no relationship exists:

Total Sales (Base) =
SUMX(
  Sales,
  COALESCE(Sales[Amount],0)
  * COALESCE(
      LOOKUPVALUE( ExchangeRate[Rate], ExchangeRate[Currency], Sales[Currency] ),
      1
    )
)

Notes:

• Ensure ExchangeRate[Rate] represents rate to your chosen base currency and is up-to-date.
• Consider returning BLANK() when a rate is missing to surface data quality issues instead of silently using 1.