BI Tools: Tableau, Power BI, and Looker Questions

Troubleshooting steps

1. Reproduce and baseline: open the report in Desktop and enable Performance Analyzer (View tab > Performance Analyzer > Start Recording).
2. Interact with the slow visual and read the breakdown Performance Analyzer gives per visual: 'DAX Query' (time spent getting data), 'Visual Display' (time spent drawing it), and 'Other' (waiting on other visuals). For example, a typical trace might show: Visual Display 90ms, DAX Query 2,400ms, Other 30ms. That gap makes the culprit obvious: here, almost all the time is in the DAX query, so start there rather than touching the visual.
3. Copy the DAX query from Performance Analyzer and paste it into DAX Studio, then run it with 'Server Timings' turned on.
   • 'Server Timings' is a DAX Studio feature that splits the query's execution time into two engines: the Storage Engine (SE), the fast, multi-threaded engine that scans Power BI's compressed in-memory data (this compressed in-memory format is called VertiPaq, Power BI's columnar storage engine for Import-mode tables), and the Formula Engine (FE), a single-threaded engine that handles row-by-row logic DAX can't push down to storage (iterators, complex FILTER logic, or DirectQuery translation).
   • Worked example: Server Timings might report SE = 2,100ms, FE = 300ms out of the 2,400ms total. Because SE dominates, the query is storage-engine bound, meaning the volume of data being scanned is the problem, not the DAX logic itself. Conversely, if FE dominated (say FE = 1,900ms, SE = 200ms), that points to an expensive, non-foldable calculation (nested iterators, context transition) as the bottleneck.
4. Check server-side signals: for Premium/Fabric capacities, 'Query Diagnostics' (a built-in Power BI feature that records detailed timing/trace events for a refresh or query, similar to what Performance Analyzer shows but capturable at scale) and gateway performance counters (CPU, memory, network latency on the on-premises gateway machine) can reveal whether slowness is local to your machine or systemic across all users.

Differentiate data retrieval vs rendering

• Long 'DAX Query' time in Performance Analyzer, confirmed by high SE or FE time in DAX Studio, means the problem is data retrieval: the query itself is expensive.
• If the DAX query comes back fast (say, under 200ms) but 'Visual Display' is still slow (say, 2 seconds), the problem is rendering: too many data points, a heavy custom visual, or complex conditional formatting the client has to draw.

When to optimize DAX vs simplify visuals vs change storage mode

• Optimize DAX when SE or FE time dominates: rewrite iterators with variables, replace row-by-row FILTER with SUMMARIZE/pre-aggregated logic, or add explicit measures instead of implicit ones.
• Simplify visuals when 'Visual Display' dominates: reduce the number of data points (Top N, binning), replace an expensive custom visual with a native one, or split a busy visual into a drillthrough page.
• Change storage mode when the query is DirectQuery and SE time is dominated by slow round-trips to the source database (not VertiPaq): consider Import mode, a composite model, or aggregation tables so common queries are answered from cached, in-memory data instead of the live source.

Outcome
Collect the metrics above, fix the highest-cost layer first (usually the query/data layer), then re-run Performance Analyzer to confirm the improvement before touching anything else.

Approach: use a time-intelligent measure that sums Revenue, computes prior-month revenue using PREVIOUSMONTH (or DATEADD for custom calendars), and computes percent change with DIVIDE to handle zero/BLANK safely. Return BLANK or 0 according to reporting needs.

DAX measure:

Total Revenue = SUM(Sales[Revenue])

Revenue Prev Month =
CALCULATE(
  [Total Revenue],
  PREVIOUSMONTH('Date'[Date])
)

MoM % Growth =
VAR Curr = [Total Revenue]
VAR Prev = [Revenue Prev Month]
VAR Pct = DIVIDE(Curr - Prev, Prev, BLANK())  // returns BLANK if Prev is 0 or BLANK
RETURN
IF( ISBLANK(Curr) && ISBLANK(Prev), BLANK(), Pct )

Notes:

• Use DIVIDE to avoid divide-by-zero errors; third arg controls fallback (0 or BLANK).
• For running comparisons across fiscal months, replace PREVIOUSMONTH with DATEADD('Date'[Date], -1, MONTH) with CROSSFILTER adjusted for custom calendars.
• If you prefer to show 100% growth when Prev=0 and Curr>0, use DIVIDE(Curr - Prev, IF(Prev=0, Curr, Prev), 1) or custom logic.

Validation strategy:

• Unit tests with synthetic data: months with Prev=0 & Curr>0, Prev>0 & Curr=0, both zero, missing months.
• Visual checks: table visual listing Date, Curr, Prev, MoM% to inspect row-level correctness.
• Aggregation tests: validate at month, quarter, year, product, region levels — ensure measure remains filter-safe (use CALCULATE + ALLSELECTED if needed).
• Tooling: use DAX Studio to run queries, check performance and evaluate context transition.
• Edge cases: gaps in Date table (ensure continuous Date table marked as Date), multiple fact rows per month, currency conversions, slicer interactions (ensure measure respects current filters).

Comparison and recommendation for mid-sized company (100 users):
Tableau: strong visualization features, mature admin controls, and good embedding (Tableau Server/Embedded). Admin overhead moderate—needs dedicated server/IT for scaling. Licensing cost is higher per user. Good connectors to warehouses.
Power BI: low admin overhead for Microsoft shops, very cost-effective licensing, good embedding via Power BI Embedded, and solid governance with Azure AD. Best if company already uses Microsoft stack; weaker advanced viz compared to Tableau.
Looker: modern governed semantic layer (LookML) enabling centralized metrics and strong data governance. Embedding is robust via APIs. Licensing tends to be enterprise-priced but offers good scale and consistency across teams.
Recommendation: For a mid-sized company seeking governed self-serve analytics with moderate admin cost, choose Looker if consistent metrics and data governance are top priorities and budget allows. Choose Power BI if cost and Microsoft ecosystem integration matter more. Choose Tableau if advanced visual capabilities and analyst-driven exploration are primary. For most mid-sized firms wanting balance of governance and ease-of-use, I’d pick Looker for strong semantic layer and embeddability, enabling consistent metrics and self-serve without exploding admin overhead.

Design philosophy
Use the same dataset but separate report pages tailored to each audience: executive (monthly) and operations (daily).

Executive dashboard (monthly)

• Visual choices: KPI cards, trend lines, variance charts, small multiples for top-level segments.
• Aggregation level: monthly, region/product roll-up; pre-aggregated measures for speed.
• Interactivity: slicers for time/region, tooltips, and focused drill-to-detail links (not full-table views).
• Navigation: landing page with key KPIs and links to supporting pages.
• Refresh cadence: daily or nightly is sufficient; data latency acceptable up to 24 hours.

Operations dashboard (daily)

• Visual choices: tables, gauge/tile indicators, real-time cards, detailed time-series with minute/hour granularity where needed.
• Aggregation level: transactional or hourly; enable drill-through to session/order details.
• Interactivity: filters for queues, search, row-level detail, alerts for thresholds.
• Refresh cadence: near real-time or frequent scheduled refreshes (hourly) using DirectQuery or frequent incremental refresh.

Handling conflicting requirements

• Provide aggregated datasets & precomputed measures for executives; expose drill-through and a separate operations report for analysts.
• Implement role-based bookmarks and Row-Level Security to tailor content.
• Use performance isolation: large, detailed queries kept to the Ops report (different pages or different workspace) to avoid slowing executive views.

This approach gives executives fast, curated KPIs while allowing analysts to explore transactional detail without compromising performance.

The three modes, in plain terms

• Import mode: Power BI copies the source data into its own compressed in-memory storage (called VertiPaq). Once imported, every visual query hits that fast local copy, not the original source. Freshness is only as good as the last scheduled refresh; there's a real cost to importing 200 million rows (a refresh of that size can realistically take tens of minutes depending on the source and hardware, and it repeats on every scheduled cycle), and users only ever see data as fresh as the last completed refresh.
• DirectQuery: Power BI stores no data at all. Every time someone clicks a slicer or opens a visual, it sends a live query straight to the source system and waits for the answer. Data is always current, but every interaction depends on how fast the source can answer, and a poorly-indexed 200-million-row table can make the report feel sluggish.
• Live Connection: similar to DirectQuery in that no data is copied, but instead of connecting straight to a raw database, it connects to an already-built shared model, most often Azure Analysis Services (AAS, Microsoft's fully managed cloud service for hosting one central data model that many different reports can reuse) or another published Power BI dataset. You don't build your own tables/measures in Desktop here, you're just building a report on top of someone else's already-modeled data.

Modeling/DAX limits
Import supports full DAX, including calculated columns and calculated tables. DirectQuery restricts some functions (e.g., certain time-intelligence patterns behave differently) and disallows calculated columns/tables in many cases, since those would require materializing values Power BI doesn't store. Live Connection means you're not modeling at all locally, the model lives wherever it's hosted.

Applying this to the 200-million-row, hourly-update, near-real-time scenario
Fully importing 200 million rows on an hourly cycle is a stretch: even if the refresh completes in time, users still only see hourly-old data at best, and refresh load on the source is heavy every single hour. Fully DirectQuery keeps it truly current but every dashboard interaction becomes a live 200-million-row query, likely to feel slow for interactive drill-down.
A composite model is the realistic answer: import a small aggregated table, e.g., hourly totals by store and product (this might shrink 200 million transaction rows down to a few hundred thousand summary rows), so the default executive views hitting that aggregate table stay fast and refresh quickly. Leave the full 200-million-row detail table in DirectQuery, only queried when someone actually drills into row-level detail. Power BI's automatic aggregation feature can route a visual to the fast imported summary table automatically and only fall back to the slow DirectQuery detail table when the visual needs specifics the summary can't answer.