Home › BI and analytics › Power BI vs Databricks AI/BI

BI and analytics · free guide · three pages

Power BI vs Databricks AI/BI, or both, on a lakehouse

Your data already sits in a lakehouse (Delta tables in Databricks). Which BI tool should people use, where should the business definitions live, where should row-level security live, how does each handle big data, and what does it cost? This page decides. The two in-depth pages explain how each tool works inside, how to troubleshoot, benchmark and test it, and what goes wrong in production.

The short answer. Use both, with one rule: business definitions (metric views) and row-level security (row filters driven by one entitlements table) live once, in Databricks Unity Catalog. AI/BI dashboards, Genie and agents read them directly. Power BI is a thin reader for formal reporting in Microsoft 365 companies. If Power BI copies data (Import or Direct Lake), it applies a role that reads the same entitlements table.

In plain words

Three things a BI tool needs

Every BI setup needs three things. Data: the tables, here gold Delta tables in the lakehouse. Meaning: the semantic model, which says what 'revenue' or 'on-time delivery' means, how tables join, and which filters apply. Permission: row-level security (RLS), which decides that a sales manager in the North sees only North rows.

Power BI and Databricks AI/BI can both hold the meaning and the permission. The question is not which tool is better. The question is where meaning and permission should live so that every tool, and every AI agent, gives the same number to the same person.

Gold Delta tablesin the lakehouseUnity Catalogmetric views (the numbers)row filters + masksentitlements table(who may see what)AI/BI dashboardsas the viewerGeniequestions in plain wordsAgentstyped tools, as the userPower BI DirectQuery+ single sign-onPower BI Importor Direct Lake (a copy)Power BI modelthin: measures 1:1with metric views;RLS role reads thesame entitlementscopy leaves Unity CatalogBlue and lavender: Unity Catalog rules apply, because the query runs as the person looking.Rose: data was copied out, so Power BI must apply the rules itself.

The recommended picture. Meaning and permission live once in Unity Catalog. Tools that query as the person looking get the rules for free; Power BI copies get a thin model and a role that reads the same entitlements table.

Decision 1

Where should the semantic model live?

A. Databricks side onlyDatabricks: metric views holdevery definitionPower BI: Power BI model is thin:simple measures thatmatch metric viewsPursue (default)B. Both sides, by handDatabricks: metric viewsone versionPower BI: DAX measuresa second versionDrop: numbers driftC. Power BI side onlyDatabricks: Databricks onlystores tablesPower BI: the Power BI modelis the only truthOnly if Power BI is the sole consumer

Three choices. Keep definitions in one place; a second hand-written copy always drifts.

OptionWhat happensVerdict
A. Databricks side: metric views hold every definition; Power BI keeps a thin model whose measures match them one to oneOne truth for dashboards, Genie, agents and Power BI. Power BI still needs a model (it cannot run without one), but the model holds no business logic of its own. An automated test checks every Power BI measure against its metric view.Pursue (default)
B. Both sides, each written by handTwo teams, two languages (SQL/YAML and DAX), two release cycles. Within months 'revenue' differs by a filter. This is the most common real-world mess.Drop
C. Power BI side only: Databricks stores tables, the Power BI model is the semantic layerFine when Power BI is the only consumer and nobody else needs the numbers. Breaks the day you add Genie, an agent, an API or a second BI tool: they cannot read DAX measures.Only if Power BI is the sole consumer, forever
B, done right: definitions in one source (metric-view YAML), Power BI measures generated or checked from itBoth sides exist, but one is derived from the other and a test fails the build if they disagree.Pursue when Power BI is large

What a thin Power BI model looks like: tables or views from gold, relationships, and measures that are simple sums, counts or ratios with the same names and filters as the metric views. No calculated business rules that exist only in DAX. A nightly test runs each Power BI measure (through the XMLA endpoint or the executeQueries API) and the same metric view in Databricks, and fails if they differ.

Can Power BI read metric views directly? Support for reading Unity Catalog metric views from outside Databricks is still changing (check the current Databricks and Power BI connector notes). If your setup cannot, point Power BI at gold aggregate views generated from the same metric-view YAML, so the definition still lives in one file.

Decision 2

Where should row-level security live?

OptionWorks forFails forVerdict
Databricks side only (Unity Catalog row filters and column masks)Works for every consumer that queries as the person looking: AI/BI with viewer credentials, Genie, agents acting on behalf of the user, Power BI DirectQuery with single sign-on.Power BI Import or Direct Lake: the data is copied out, so the filter is not there. Power BI DirectQuery with a shared service account: Databricks sees one user.Pursue (default)
Power BI side only (roles in the model)Works inside Power BI for Import, Direct Lake and DirectQuery.Nothing else is protected: an analyst in Databricks, Genie or an agent sees every row. Workspace admins, members and contributors skip Power BI RLS entirely.Drop as the only layer
Both sides, driven by one entitlements tableNeeded whenever Power BI uses Import or Direct Lake. Both rules read the same gold table (who may see which region, plant or customer), so access is changed in one place.Two enforcement points to test. The test suite runs the same user on both sides and expects the same row count.Pursue when Power BI copies data
Both sides, each kept by hand (AD groups in Power BI roles, different groups in Unity Catalog)Looks fine on day one.Someone is removed in one place and not the other. Auditors find it.Drop
Two traps that leak data. (1) An AI/BI dashboard published with the publisher's credentials runs every query as the publisher, so every viewer sees the publisher's rows. Publish secured dashboards with viewer credentials. (2) Power BI RLS does not apply to workspace Admins, Members and Contributors. Give report readers the Viewer role or an app audience, never Member.

Decision 2, continued

How RLS works on both sides at once

entitlements table (gold)user_email · regionUnity Catalog row filtera SQL function on the table:current_user() in entitlements?Power BI dynamic RLS roleDAX: [user_email] =USERPRINCIPALNAME()imported like any tableAI/BIviewer credentialsGenie, agentsPower BI DirectQuery+ SSO (both apply)Power BI Import, Direct Lake(Unity Catalog cannot see these)

One entitlements table, two enforcement points. With DirectQuery and single sign-on both apply, and the user sees the overlap.

The entitlements table lives in gold and is owned by the data team, fed from HR or your identity system: one row per person (or group) per thing they may see, such as user_email, region.

-- Unity Catalog: a row filter function, then attach it to the table
CREATE FUNCTION sec.region_filter(region STRING) RETURN
  is_account_group_member('bi_all_regions')
  OR EXISTS (SELECT 1 FROM gold.entitlements e
             WHERE e.user_email = current_user() AND e.region = region);

ALTER TABLE gold.sales SET ROW FILTER sec.region_filter ON (region);

-- A column mask: only HR sees salaries
CREATE FUNCTION sec.mask_salary(v DECIMAL(12,2)) RETURN
  CASE WHEN is_account_group_member('hr') THEN v ELSE NULL END;
ALTER TABLE gold.employees ALTER COLUMN salary SET MASK sec.mask_salary;

Databricks adds the filter to every query on Databricks compute (SQL warehouses, serverless, standard clusters), including queries from metric views, dashboards, Genie and Power BI DirectQuery.

// Power BI: a role on the imported Entitlements table
// Model: Entitlements[region] 1 ─► * Sales[region]  (filter flows to Sales)
[user_email] = USERPRINCIPALNAME()

Power BI: the role filters the small Entitlements table and the relationship carries the filter to the fact table. Members of the role are Entra ID groups, assigned in the service.

What the user sees in DirectQuery with single sign-on: Power BI's role filters first, then the SQL reaches Databricks as that user, so the Unity Catalog filter also applies. The user sees rows allowed by both. With one shared entitlements table the two always agree.

Why not just one side? Unity Catalog cannot see inside a Power BI Import or Direct Lake model: those read a copy or the files directly. Fabric cannot mirror Databricks tables that have row filters or column masks (check the current mirroring limits), which pushes you to Power BI roles for Fabric-side copies.

Decision 3

How each handles big data

ModeWhere the data sitsWhat limits itGood for
Power BI ImportCompressed copy in memory (often 5 to 10 times smaller than Parquet text, depends on cardinality)Model memory limit per capacity size; 1 GB per model on Pro; refresh time windows; refresh doubles memory while it runsGold aggregates up to tens of millions of rows; fastest clicks
Power BI DirectQueryNothing copied; every visual is a SQL queryEach visual waits on the warehouse; a 1 million row cap on intermediate results; limited parallel connectionsBillions of rows, if you add aggregation tables and a well-clustered gold layer
Power BI Direct LakeDelta files in OneLake loaded into memory column by column when first askedGuardrails per capacity size (rows per table, files and row groups per table, memory); beyond them it falls back to DirectQuery or failsLarge Fabric-native data; Databricks data only through shortcuts or mirroring
Databricks AI/BINothing copied; every widget is a SQL query on a SQL warehouseWarehouse size and number of clusters; result-size caps per widget; cost grows with clicksAny size the warehouse can scan quickly; clustered gold tables and materialized views keep it fast

The rule that matters most is the same for both tools: build gold aggregates for dashboards. A dashboard that scans 4 billion raw rows to draw 12 monthly bars is slow and costly in any tool. Pre-aggregate by day and the main dimensions, cluster on the filter columns, and keep the raw detail for drill-through only.

Decision 4

Cost comparison

Example: 25 report authors, 500 viewers, used 10 hours a day on 22 working days. US list prices as known in November 2026, rounded; your discounts, region and Microsoft 365 agreement change them a lot. Databricks serverless SQL at about $0.70 per DBU; a Small warehouse is 12 DBU an hour and a Medium is 24.

SetupBI licences per monthDatabricks compute per monthTotal per monthComment
Power BI Pro for everyone, Import525 × $14 = about $7,350refresh only: about 22 hours of Small = about $185about $7,500Simple; cost grows with every viewer
Fabric F64 (viewers free) + 25 Pro authors, Import or Direct LakeF64 about $5,000 reserved (about $8,400 pay-as-you-go) + $350about $185 for refreshabout $5,500 to $9,000Pays off above roughly 400 viewers; capacity also runs other Fabric work
Power BI Pro for everyone, DirectQueryabout $7,350every click: Medium for 220 hours = about $3,700about $11,000The worst of both bills; use only where freshness or Unity Catalog RLS demands it
Databricks AI/BI onlynone (no per-viewer licence)Small to Medium for 220 hours = about $1,850 to $3,700; auto-stop lowers itabout $2,000 to $4,000Cheapest for many occasional viewers; costs follow usage
Both: Power BI for 100 report users, AI/BI and Genie for the rest100 × $14 = $1,400 (often $0 if Microsoft 365 E5 already includes Pro)about $2,000 to $3,000about $2,000 to $4,500Usually the best value in a Microsoft shop

Rough, unverified list-price estimates. Run your own numbers in the Databricks cost calculator and with your Microsoft agreement. Two things move the answer most: whether Microsoft 365 E5 already includes Power BI Pro for everyone, and how many viewers open dashboards only a few times a month.

Decision 5

Which tool for which job

JobUseWhyVerdict
Operations, platform and data-trust dashboardsAI/BINext to the data, no licences, Unity Catalog rules apply directly.Pursue
Plain-language questions from business usersGenie over metric viewsAnswers use the same certified definitions as the dashboards.Pursue
Agents that need numbersTyped tools over metric views (or the Genie API)Agents call tools; they never write free SQL against raw tables.Pursue
Board packs, finance, paginated and printed reports, Excel, TeamsPower BIStronger formatting, paginated reports, Excel and Teams; Pro is often already paid for.Pursue in a Microsoft 365 company
Heavy self-service modelling by analysts who know DAXPower BI on a certified thin modelLet them build reports, not new definitions of revenue.Pursue with guardrails
A company with no Microsoft estateAI/BI onlyPower BI would add licences, a second model and a second set of security rules for little gain.Drop Power BI
Power BI Copilot as the agent layer—It works only over a Power BI model and needs Fabric capacity; it becomes a second source of truth for AI.Drop

Putting it together

The recommended architecture

  • Gold: Delta tables, liquid-clustered on the columns dashboards filter by, plus pre-aggregated gold tables or materialized views for dashboards.
  • Semantic layer: Unity Catalog metric views, written in YAML, kept in git, certified. The only place a business number is defined.
  • Security: one entitlements table in gold; Unity Catalog row filters and column masks read it; Power BI roles read it only where Power BI copies data.
  • AI/BI dashboards for operations and engineering, published with viewer credentials when data is sensitive.
  • Genie over metric views only, with instructions, example questions and benchmarks. Agents call typed tools over metric views.
  • Power BI: a thin, certified model. DirectQuery with single sign-on for sensitive, fresh data; Import for small, non-sensitive, heavily used aggregates; Direct Lake only if the company runs on Fabric and the data is in OneLake.
  • Tests: the same-number test (Power BI measure equals metric view), the same-rows test (one user sees the same row count in both tools), and a performance budget per dashboard (see the in-depth pages).

Swapping tools later is then cheap: a new BI tool is one more reader of the same metric views and the same security rules.

FAQ

Questions people ask

Should the semantic model live in Databricks or in Power BI?

Keep the definitions in Databricks (Unity Catalog metric views) and keep the Power BI model thin, with measures that match the metric views one to one and a test that checks they agree. Power BI always needs a model to run, but it should not invent its own business logic.

Where should row-level security live?

In Unity Catalog, as row filters and column masks, driven by one entitlements table. Add a Power BI role that reads the same table only when Power BI copies data out (Import or Direct Lake), because then Unity Catalog cannot see the query.

Does Unity Catalog row-level security apply to Power BI?

Only in DirectQuery with single sign-on, where each Power BI user's own identity reaches Databricks. With Import, Direct Lake, or a shared service account, it does not, and Power BI must apply its own role.

Is Databricks AI/BI cheaper than Power BI?

For many occasional viewers, usually yes: AI/BI has no per-viewer licence and you pay only for warehouse time. For a few heavy users in a company that already pays for Microsoft 365 E5, Power BI can cost almost nothing extra. DirectQuery from Power BI pays both bills.

Can we use both?

Yes, and many companies should: AI/BI and Genie for operations, engineering and plain-language questions, Power BI for formal reporting in Microsoft 365. The rule is one set of definitions and one entitlements table under both.

Read on

The in-depth pages

Power BI and Fabric, in depth: how it works inside, Direct Lake, RLS mechanics, troubleshooting, benchmarking, testing, production problems. Databricks AI/BI, in depth: the same for AI/BI dashboards and Genie. Related: the semantic layer · Genie and metric views · know your consumers.