BI Tools: Tableau, Power BI, and Looker Questions

Approach: Margin must be calculated per row as Quantity * (UnitPrice - UnitCost) and then summed. SUMX is required because it iterates row-by-row and evaluates the expression in a row context. SUM can only aggregate a single column’s values and cannot compute the per-row product.

Optimized measure (recommended):

Total Margin =
SUMX(
  Orders,
  Orders[Quantity] * ( Orders[UnitPrice] - Orders[UnitCost] )
)

Why SUMX (concise):

• SUMX creates a row context across Orders, evaluates the arithmetic for each row, then sums the results. Using SUM(Orders[Quantity]) * SUM(Orders[UnitPrice] - Orders[UnitCost]) would be incorrect because it aggregates components separately and loses row-level relationships.

Performance considerations for large volumes:

• If Orders is very large and UnitPrice/UnitCost are stable per row, precomputing a calculated column (Orders[RowMargin] = Orders[Quantity]*(Orders[UnitPrice]-Orders[UnitCost])) and then using:
  Total Margin = SUM(Orders[RowMargin])
  will be faster at query time because the work is done at model refresh and SUM is highly optimized.
• If you must calculate at query time (dynamic currency or volatile logic), limit iteration scope by using the current filter context: SUMX( VALUES(Orders[OrderID]), ... ) or SUMX( SUMMARIZECOLUMNS(...), ... ) to pre-aggregate when there are many line-level rows per order.
• Ensure numeric columns are the correct types, avoid unnecessary RELATED calls inside SUMX, and prefer measures that let engine push aggregations to storage engine. Monitor with DAX Studio (query plan/STATISTICS) to detect expensive scans.

Filters vs parameters — short definition and tool differences:

• Filters: apply conditions to data (e.g., where country = 'US') and are typically pushed to the data source as SQL WHERE clauses or folded into the query. In dashboards they control which rows are returned/visible.
• Parameters: single-value (or small set) user inputs that can be referenced in calculations, dynamic titles, or to build SQL; parameters don’t inherently filter rows unless used in a filter expression or calculation.

Tool-specific notes and examples:

• Tableau:
  • Filters: worksheet/visual-level filters, context filters — push down to source when possible. Use for user-driven selections (date range, region).
  • Parameters: a single value the user picks (e.g., threshold = 10000). Use for scenario modelling ("what if" threshold), choosing which measure to show (dropdown to pick Revenue vs Profit), or driving a calculated field. Parameters only affect queries if referenced in calculated fields/filters; older Tableau parameters are workbook-local and not query-folded by themselves.
• Power BI:
  • Filters / slicers: visual/page/report filters; applied as query filters or DAX filter context. Use slicers for user-driven filtering like product category.
  • Parameters: two types — Query (Power Query) parameters that can be folded into source queries (good for query-time filtering like environment, date anchor), and What-If parameters (DAX tables) used for scenario modelling and dynamic measures. Use Query parameters when you want the parameter to limit data at refresh/query time; use What-If for on-the-fly scenario analysis in visuals.
• Looker:
  • Filters: Explore filters generate WHERE clauses and are pushed to SQL; use for user selections and drill-downs.
  • Parameters (LookML): user inputs that can be interpolated into SQL via templating/liquid (e.g., choose aggregation or pass a pivot value). Use for dynamic SQL, measure selection, or scenario inputs. Be careful: complex templating can prevent Looker from generating efficient pushed-down filters.

When to use which (concrete):

• User-driven data selection (pick region, date range): use filters/slicers — they reduce returned rows and are intuitive.
• Scenario modelling or "what-if" (adjust price, margin, test impact): use parameters/what-if; parameters feed calculated measures without necessarily re-querying all data.
• Dynamic measure selection (dropdown to switch KPI displayed): use parameter controlling calculated measure or a CASE in LookML/DAX.

Performance considerations:

• Filters usually allow query folding / WHERE pushdown, reducing row scan and improving performance.
• Parameters can be efficient if they are query parameters that fold to the source (Power Query params, Looker templated parameters that become WHERE clauses). But parameters used inside client-side calculations, or embedded in complex SQL generation, may prevent pushdown and cause larger scans or client-side evaluation — harming performance.
• Best practice: prefer filters or query-folding parameters to reduce data returned; when using parameters for dynamic logic, test generated SQL and ensure the database can optimize the resulting query (indexes, sargability).

Using VAR in DAX improves readability by naming intermediate results and can improve performance by avoiding repeated expensive calculations (filtering, SUMX, CALCULATE) — VARs are evaluated once per evaluation context and then reused.

Example — measure that recalculates the same intermediate table/scalar multiple times:

TotalProfitMargin%
= DIVIDE(
    SUMX(
      FILTER( Sales, Sales[Region] = "North" && Sales[Year] = 2024 ),
      Sales[Profit]
    ),
    SUMX(
      FILTER( Sales, Sales[Region] = "North" && Sales[Year] = 2024 ),
      Sales[Revenue]
    )
)

Problems:

• The FILTER expression runs twice (same expensive row evaluation).
• Harder to read and maintain.

Refactored with VAR:

TotalProfitMargin% =
VAR North2024 =
    FILTER( Sales, Sales[Region] = "North" && Sales[Year] = 2024 )
VAR TotalProfit = SUMX( North2024, Sales[Profit] )
VAR TotalRevenue = SUMX( North2024, Sales[Revenue] )
RETURN
DIVIDE( TotalProfit, TotalRevenue )

Why this helps:

• Readability: each step has a clear name; intent is obvious.
• Performance: FILTER and row iteration over Sales for that subset run once per context; SUMX operates over the cached VAR result, reducing repeated scanning and CPU.
• Maintainability: change the filter logic in one place.

Caveats:

• VAR stores a reference/evaluated result for the current context; for very large intermediate tables, materializing them in memory can increase memory pressure — consider using aggregations or CALCULATE with measures when appropriate.
• Always test with Performance Analyzer on real model data to measure actual gains.

Approach:

• Use an INCLUDE LOD to compute each customer’s average order value (AOV) at the customer granularity while the view is at Category.
• Then use a FIXED LOD (or an aggregate in the view) to take the Category-level average of those per-customer AOVs.

Example calculations (assume fields: [Category], [Customer ID], [Order ID], [Order Value]):

Per-customer AOV (customer-level AOV regardless of view):

{ INCLUDE [Customer ID] : AVG([Order Value]) }

Category-level average of per-customer AOVs (returns one value per Category):

{ FIXED [Category] : AVG( { INCLUDE [Customer ID] : AVG([Order Value]) } ) }

Why INCLUDE is used:

• INCLUDE [Customer ID] tells Tableau to compute the inner AVG([Order Value]) at the customer granularity (each customer’s average across their orders), even if the current viz is at the Category level. Without INCLUDE, AVG([Order Value]) at Category would average across orders, which biases customers with more orders.
• The outer FIXED [Category] then averages those per-customer AOVs to produce the Category-level metric (one AOV per Category representing the mean of customers' AOVs).

How filters affect the result:

• Data source filters and context filters are applied before LOD computation. If you want a filter (e.g., Region, Product Subset, or date range) to affect the per-customer AOV calculation, add it to Context so the LOD respects it.
• Non-context dimension filters (regular filters) are applied after LODs: they will filter the displayed rows but won’t change the already-computed LOD values. For example, a worksheet quick filter on Category (not in Context) will change what categories are shown but not recompute the per-customer AOV unless that filter is a context filter.
• Measure filters (e.g., filter out orders with Order Value < 1) should be converted to context or applied as data source calculations if you need them to impact the LOD.

Edge notes:

• If customers span multiple categories, the INCLUDE with FIXED [Category] ensures per-customer AOV is evaluated per customer across all their orders; if you need per-customer-per-category AOV instead, use INCLUDE [Customer ID], [Category] or use FIXED [Category],[Customer ID].

Power BI — Expert (3 years)

• Duration: 36 months, daily use
• Dashboard 1: Executive Sales & Margin Scorecard — purpose: track monthly revenue, gross margin, and top customers. Key visuals: KPI tiles, month-over-month trend line, stacked column by region, drill-through to customer-level table. Data sources: Azure SQL Data Warehouse, CSV sales exports, Excel targets.
• Dashboard 2: Inventory Aging & Reorder Alerts — purpose: minimize stockouts and deadstock. Key visuals: heatmap by SKU age, threshold-based alert cards, reorder recommendation table. Data: Snowflake, ERP API.
• Responsibilities: building & publishing PBIX to Power BI Service, scheduled dataset refreshes (gateway), row-level security, app workspace permission management, embedding visuals in internal SharePoint.

Tableau — Advanced (24 months)

• Duration: 24 months across two projects
• Dashboard 1: Marketing Funnel Attribution — purpose: measure channel conversion. Visuals: Sankey-style flow (parameterized), cohort charts, campaign-level ROI table. Data: Redshift, Google Analytics export.
• Dashboard 2: Customer Support Ops — purpose: reduce SLA breaches. Visuals: stacked bar by priority, calculated SLA burn-down, agent performance heatmap. Data: Postgres, Zendesk.
• Responsibilities: published workbooks to Tableau Server, scheduled extracts, managed project permissions, optimized extracts and use of LOD calculations.

Looker — Basic/Intermediate (18 months)

• Duration: 18 months (embedded analytics project)
• Report 1: Product Usage Analytics — purpose: monitor feature adoption. Visuals: time-series, funnel steps, cohort retention. Data: BigQuery via LookML models.
• Report 2: Revenue Forecast Model View — purpose: pipeline to revenue mapping. Visuals: trend lines, scenario toggles using persisted derived tables.
• Responsibilities: authored LookML models, created dashboards for embedding in product, controlled user access via roles, collaborated with engineering on API-based embedding.

Overall I select tools based on data sources, embedding needs, and stakeholder self-service maturity; I maintain governance through scheduled refreshes, RLS/roles, and clear workspace permission policies.