Power BI in one sentence
If you've ever looked at a car's dashboard, you already understand Power BI. A dashboard takes readings from dozens of different parts of the car (the engine, the fuel tank, the tires) and boils them down into a handful of gauges the driver can glance at in a second. Power BI does the same thing for a business: it pulls data from different systems and turns it into a small number of charts and numbers that someone can understand at a glance, instead of digging through raw records one by one.
Connecting Power BI to Dataverse
There are two main ways to get CRM data into Power BI, and which one you pick depends on how much data you have, how often it needs to refresh, and how much cleanup the data needs before it's ready to show someone.
The native Dataverse connector
Power BI Desktop has a built-in connector for Dataverse (Microsoft's business database that sits underneath Dynamics 365, storing records like accounts, contacts, and cases). It queries the tables directly, which is the simplest option for a straightforward report over a reasonable amount of data. It's a good default choice when you're building a report against a handful of tables and don't need to share heavily reshaped data across several different reports.
Dataflows
Dataflows sit a level above a raw connection. They use a tool called Power Query to pull data out, clean it up, and store it in a reusable, refreshable location that multiple reports (and multiple people building reports) can pull from, instead of everyone rebuilding the same cleanup steps from scratch. Think of a dataflow as meal-prepping ingredients once at the start of the week, so every recipe afterward can just grab what it needs instead of chopping the same vegetables over and over. It's worth setting one up once more than one report needs the same cleaned-up version of, say, Opportunities combined with a custom Product Category list.
Organizing the data: the star schema
Good Power BI reports are built on a layout called a star schema. It sounds technical, but the idea is familiar: think of a store receipt. Each line on the receipt is a fact — "bought 2 units of this item, on this date, for this amount." The receipt line itself doesn't explain what the item actually is; for that you'd flip to a catalog that spells out what each product code actually means, who the customer is, and so on. In a star schema, the receipt lines become the fact table, and the catalogs become dimension tables.
Mapped onto typical Dynamics 365 CE data:
- Fact table: Opportunities (or Opportunity Line Items for product-level detail) — this is where the numbers you want to measure live, like estimated value, actual revenue, and how many deals closed.
- Dimension tables: Accounts, Contacts, Products, Territories, and a Date table — these are the "catalogs" that describe the who, what, and when behind each fact, and they're what you filter and group by.
Keeping fact tables narrow (mostly ID numbers and measurements) and dimension tables descriptive (names, categories, labels) keeps the whole model fast and the relationships easy to predict. A dedicated Date table, specifically marked as the model's date table, is worth the extra setup effort even though it feels unnecessary at first — it's what makes "compare this year to last year" style calculations work reliably.
DAX essentials
DAX is the formula language behind Power BI's calculations. Two of its building blocks look similar on the surface but behave very differently.
Measures vs. calculated columns
This is the distinction that trips up almost everyone coming from Excel. A calculated column is worked out once for every row when the data refreshes, and then stored permanently in the model, like a price tag that's already printed and stuck onto an item before it ever reaches the shelf — it doesn't change no matter how you're currently browsing the store. A measure, on the other hand, is calculated fresh, on the spot, based on whatever you're currently looking at — more like the running total at a checkout that updates in real time as items get scanned, always reflecting exactly what's in front of you right now. Measures are the right default for totals, averages, and ratios, because they automatically adjust to whatever filter, chart, or slicer the user currently has applied.
// Calculated column - a price tag baked in ahead of time, stored per row
Opportunity Size Band =
IF(Opportunities[estimatedvalue] > 100000, "Large", "Standard")
// Measure - a running total calculated fresh each time
Total Pipeline Value =
SUM(Opportunities[estimatedvalue])Common DAX patterns
Won Revenue =
CALCULATE(
SUM(Opportunities[actualvalue]),
Opportunities[statuscode] = "Won"
)
Account Industry (from related table) =
RELATED(Accounts[industrycode])
Open Pipeline by Owner =
CALCULATE(
SUM(Opportunities[estimatedvalue]),
Opportunities[statecode] = "Open",
ALLSELECTED(Opportunities[ownerid])
)CALCULATE does most of the heavy lifting in real reports. In plain terms, it lets you say "add or override the current conditions before doing this sum" — which is exactly what a measure like "revenue we've won" or "deals still open" needs to do: start from the total, then narrow it down to just the rows that matter.
Relationships and cardinality
Once your fact and dimension tables are loaded, the connections between them need to be set up correctly, or your measures won't filter the way you expect. Most CRM-based relationships are "one-to-many" running from a dimension to a fact — one Account can have many Opportunities, one Product can appear on many Opportunity Line Items — and by default, filtering flows from the "one" side down to the "many" side, similar to how filtering a catalog by "electronics" naturally narrows down every receipt line tied to an electronics purchase. Turning on filtering in both directions at once can look like a quick fix when a report isn't behaving, but it's worth understanding why the filter isn't working as expected first, since flipping it on everywhere can quietly create confusing, contradictory filter paths once a model has several fact tables.
Row-Level Security (RLS): the same filing cabinet, different views
Row-Level Security restricts which rows of data a person is allowed to see in a report, based on who they are, without needing to build and maintain a completely separate report for every audience. Picture one shared filing cabinet used by an entire company, where each employee can only open the drawers relevant to their own department — everyone's looking at the same cabinet, but each person's view of it is filtered. The way this is built is by defining one or more roles in Power BI Desktop, each with its own filter rule, and then assigning users (or, in a larger company, whole groups of users at once) to those roles after the report is published.
// Role: "SalesTeamOnly", filter on Territory dimension
[TerritoryManagerEmail] = USERPRINCIPALNAME()A common approach in a larger company is to mirror the same territory or business-unit structure already used for security inside Dynamics 365 itself, so a regional sales manager sees the exact same slice of data in their Power BI report that they see inside the CRM app — keeping the two in sync avoids a lot of confusing "why don't these numbers match" conversations.
There are two flavors worth knowing. Static RLS hardcodes specific values into the filter rule, which is fine for a small, fixed handful of roles. Dynamic RLS instead compares a column of data to whoever is currently logged in, using a function like USERPRINCIPALNAME() as shown above. Dynamic RLS scales much better once you have more than a handful of roles, since you maintain one single role definition plus a mapping list, rather than hand-building a brand-new role for every territory or department.
Putting Power BI reports inside a model-driven app
Instead of sending users away to a separate Power BI website, you can embed a report, or even just a single tile, directly inside a Dynamics 365 model-driven app — on a dashboard, or right on a record's form. This matters more than it sounds: a report tucked away in a separate app that people have to remember to open gets checked far less often than a tile sitting right there on the account or opportunity screen they already look at every day, the same way a car's fuel gauge gets noticed a lot more often because it's built into the dashboard instead of living in a separate app on your phone.
Under the hood, embedding works by pointing a Power BI component at a specific report and page, with the option to pass along a filter — for example, showing only the trend chart for the one account record currently open on screen, so the embedded chart feels relevant to what the user is already looking at rather than showing everything at once.
Licensing is worth checking early here too: viewing an embedded report typically requires either a paid Power BI Pro license per person, or the report living in a Premium capacity workspace that covers everyone. It's much better to confirm this at the start of a project than to discover it during final testing.
Incremental refresh: only reprocessing what changed
Once a table grows into the millions of rows — a multi-year history of Opportunities or Activities is a common culprit — refreshing the entire table from scratch every single time becomes slow and puts unnecessary strain on the source system. Incremental refresh fixes this the way a smart backup tool only backs up files that changed since yesterday, instead of copying your entire hard drive every night. It works by splitting the table into chunks based on a date column, and only reprocessing the recent chunk (say, the last 30 days) on each refresh, while leaving older, unchanged chunks alone. It's set up by defining a refresh window and an archive window on the table in Power BI Desktop, and it's one of the most effective things you can do for report performance once historical data volume becomes a real problem.
Letting Power Automate react to Power BI alerts
Power BI can watch a tile or KPI value and raise a data-driven alert when it crosses a threshold you set — for example, watching a "Cases Overdue" number. Those alerts can trigger a Power Automate flow directly, which means automation can react to a summarized trend on a dashboard, not just to a single record changing. In practice, that might mean automatically notifying a support manager and posting a Teams message the moment overdue case volume crosses a defined line, without anyone needing to sit there watching the dashboard waiting for it to happen.