Power BI Semantic Models - Simply Explained

0:00 Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. You think your power BI problem is the chart, but the chart is the easy part. Every report you build is powered by a layer underneath it called a semantic model, and it holds the tables, the links, and the calculations that turn raw data into business ready numbers. Most beginners never open that layer, so their dashboards look fine while the numbers drift. The good news is that the model is not complicated once someone explains it without the jargon. Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. By the end, you'll know exactly what the semantic model is, what's inside it, and why it decides whether your data feels fast, fresh, and trusted.

0:40 What a semantic model really is. Start with the simplest version. A semantic model is a layer of data that defines three things the tables, the relationships between them, and the calculations you want to reuse. It sits between your data source and your report, so it takes raw tables and turns them into something a report can use. Semantic sounds like a fancy word and it just means meaning. The model adds meaning to plain data. A column full of numbers is just numbers. Until the model knows that this column is revenue, that one is a date and that the two belong together. That meaning is what turns a pile of tables into a report your boss can read. The part that surprises most beginners is this every power BI report already has a semantic model. Whether the person who built it knows it or not. When you drop a chart on a page, that chart sends a question to the model, and the model answers with the numbers you see.

1:25 It also handles the filtering when you click a slice and the math. When you want a total, you build one in power BI desktop. The free tool you use to design reports and a single file holds two things at once the model and the report. The moment you publish to the power BI service those two split apart, you get a semantic model that lives on its own, and one or more reports that point at it. That split is the reason one model can feed many reports, which is the idea we'll come back to later. Think of it like a restaurant kitchen. The model is where the cooking happens, and the report is the plate that leaves it. You can restyle the plate whenever you want, but a messy kitchen ruins every dish it sends out Without a clean model. Every report has to reinvent the same math, and two reports can disagree while looking identical. One small naming note before we move on.

2:07 Plenty of people still call this a data set, and you'll see that word in older tutorials and in older menus. A data set and a semantic model are the same idea, so don't let the two names trip you up. There's one choice inside a model that changes how fast your reports feel and how fresh the numbers are, and it's the reason some teams refresh overnight while others see data the second it lands. Hold that thought because we'll get to it after we open up the model and look at what's inside. Tables, relationships and measures. Now let's open the model up. A semantic model usually holds several tables rather than one giant sheet, and each table has a clear job. Two kinds of tables show up again and again. Dimension tables describe the things in your business so products, customers, stores, and dates all live there.

2:48 They exist so you can filter and group your data. Fact tables hold the events and the numbers like each sale, each order, each temperature reading. Their whole purpose is adding those numbers up on their own. Those tables are just separate lists. A relationship is what ties them together. A relationship lets a column in one table group the data in another. So a month column in a date table can group the sales in a sales table. Picture two tables sitting side by side with a single thread between them. And that thread is the relationship. Every relationship has a one side and a many side. The one side is your dimension table where each row is unique. The many side is your fact table where the same product or date can repeat thousands of times. One detail trips people up here. Nothing in the two tables. A table as dimension or fact, and the relationship itself decides which is which based on that one to many shape.

3:31 Once the links are in place, filters travel along them. Click a single country on a report and the filter walks from the country table through the relationship into the fact table, and the numbers on the page shrink to match. Clean links make that travel fast and predictable, while messy links make the same. Click slow and confusing. Get a relationship wrong and a filter stops where you expect it to keep going. So your totals miss rows and nobody can see why. Then there are measures, and this is where the model starts doing real work. A measure is a calculation you define once and reuse everywhere, and it's worked out when someone views the report rather than stored as a column. You can let power be. I handle the simple case by dragging a number column into a chart and letting it sum. That's called an implicit measure, and it's fine for quick work.

4:11 When you want control, you write an explicit measure yourself. That brings us to Dax. The name stands for Data Analysis Expressions, and it sounds heavier than it is. Dax is the formula language for your calculations. A set of functions and operators that build new information from data already sitting in the model. The classic first example looks like this. Total sales equals sum of the sales amount column in the sales table. Read it out loud and it says for the measure called Total sales, add up every value in the sales amount column. The clever part is context. The same measure can return different answers depending on what you filter. And that's the whole trick behind Dax row context is the current row the formula is working on, and filter context is the set of filters in play at that moment. You don't need to master this today. Know that a measure isn't a fixed number.

4:51 It's a rule. The model runs fresh every time you look. One last detail for anyone who likes to know the limits, a single Dax formula can nest up to sixty four functions inside each other. You'll almost never need that many, but it's a reminder of how far these formulas can grow. There's a particular way to lay out all these tables that power BI clearly rewards, and once you see it, a lot of confusing advice suddenly makes sense. That's what we look at next. Star schema. Minus the jargon, the layout. Power BI rewards has a name and it's called a star schema. It comes from the world of relational data warehouses, and the idea is old enough to be trusted. Every table in the model gets classified as either a dimension or a fact, and then you connect them. The name comes from the shape. Put your fact table in the middle, spread your dimension tables around it and draw the relationships between them.

5:36 The result looks like a star, with one busy table at the center and simpler tables around the edges. The reason this shape works so well comes down to what a report actually does. Every visual on a report does one of two things it filters and groups, or it adds things up. A good model gives you tables built for each job. Dimension tables are what you filter and group with, while fact tables are what you summarize when the model matches the shape of the questions, the answers come back fast. For a beginner, the practical payoff is simple. A star schema is the reason power BI wants clean separate tables instead of one messy spreadsheet. You can flatten everything into a single giant table. And for a quick personal analysis, that works fine. The trouble is that one white table gets slow, hard to trust, and painful to change as it grows. Splitting it into a fact table and a few dimensions keeps it quick and keeps it honest.

6:19 There's one variation worth naming so you recognize it when you see it. Sometimes a single dimension is split across related tables. So product links to subcategory which links to category. That shape is called a snowflake. And power BI generally prefers you combine those into one table when you can. Fewer tables means fewer relationships to travel, and a simpler list for report authors to scroll through. Now, an honest caveat because this is real guidance and not a law. Microsoft says good model design is part science and part art. And sometimes you break the rules on purpose when it makes sense. So treat the star schema as the default you reach for first. Not a rule that will get you in trouble for bending. The practical takeaway is this when someone tells you to fix your data model, they usually mean one of two things.

7:01 Either your tables aren't separated into facts and dimensions, or your relationships are pointing the wrong way. Both problems live in the same place and both get easier the moment you picture the star. A tidy model is a great start, but it's still empty until you put data in it. That raises the question. Every beginner eventually hits where the data lives, and whether power BI copies it or fetches it fresh each time. Import, direct query and direct lake. This is the choice I told you to hold on to, and it's the one that decides how quickly your reports load and how current your numbers stay. It's called the storage mode. And the important part is this. It's a setting on each table rather than on the whole model. You can even mix and match inside one model. The first option is import, and it's the one most people meet first with import power.

7:42 BI copies the data into the model, stores it on disk, and loads it into memory. When someone views a report because everything sits in memory, it's quick. Power BI also compresses the data hard, often around ten to one. So roughly ten gigabytes of source data can shrink to about one gigabyte. The catch is freshness, because imported numbers are only as current as the last refresh, so you schedule refreshes to keep them up to date. A full refresh throws out all the data and reloads it, which takes time and resources, so you don't want to run one every five minutes. The second option is direct query and it takes the opposite approach. Nothing gets copied. The model keeps only the structure, the tables, the relationships, and the measures. And every time someone views a report, it asks the source database for the numbers directly. That means the data is always live, and it means size is no longer a wall you hit.

8:25 It also means you inherit the limits of the source. Direct query formulas are restricted to whatever the source can understand. Calculated tables are off the table, and heavy use can slow the whole thing down because every click becomes a live query. Picking between them comes down to two signals. The first is size. When the data is too big to copy and refresh in a reasonable window. The second is speed of truth. When the business needs numbers within seconds or minutes, and scheduled refresh is not enough for a sense of that limit. Scheduled refresh runs up to eight times a day on shared capacity and up to forty eight times a day on premium capacity. If your business needs fresher numbers than that, you're looking at Direct query. The third option is newer, and it only exists inside Microsoft Fabric. It's called Direct Lake, and it was built for the big data problem.

9:06 Instead of copying everything, it reads Delta table sitting in one lake, the single store for analytics data in fabric and it loads only the columns a query needs into memory. The effect is speed that feels like import without the full copy and the heavy refresh. The detail that makes directly click is what a refresh really does. An import refresh copies the entire data set. Again, a direct lake refresh copies only metadata, which Microsoft calls framing, and it just points the model at the newest version of the files. That kind of refresh can finish in seconds, which is why directly keeps data fresh. Without a nightly copy job, there's a trade off you should know before you get excited. Direct Lake needs a fabric capacity subscription, which is a paid tier, while import and direct query run on any power BI or fabric license, including the free one. So Direct Lake isn't the free upgrade, it's the option you reach for when your data is big, your refresh window is tight, and you already have fabric capacity in place.

9:53 That cost matters because storage mode is also a bill, and copying terabytes you rarely open is money spent on data nobody looks at. You're not locked into a single answer either. A model can mix storage modes across its tables, which is called a composite model. A common pattern puts the small dimension tables in import for fast filtering and the big fact table in direct query so it stays live. That gives you quick slices and current numbers at the same time. If that felt like a lot. Keep one plane. Rule of thumb in mind if your data is small enough to copy and you can live with a scheduled refresh, use import. If you need live numbers or the data is too large to copy, use Direct query. If you're in fabric, your data lives in one lake and you want big data to feel fast, use direct lake. Notice what just happened.

10:34 You now have a model with clean tables and a storage choice that fits your data, and it sits in one place. The last question is whether everyone in your company should build their own version, or whether they should share yours. That's where reuse and governance come in. Build it once. Reuse it everywhere. Everything so far has been about building one good model. The real payoff comes when you stop rebuilding it. If the data and the math already live in a model, you don't have to start from scratch. For every new report, you connect to the existing model and build on top of it, which saves you time and keeps every report using the same tables and the same definitions. This is the idea people mean when they say one source of truth. A shared standardized model gives report creators a reliable starting point, so their reports are accurate and the whole organization makes decisions on consistent numbers.

11:16 That's the promise, and it's worth aiming for. It's also worth being honest about the gap between the promise and reality. One well known practitioner puts it bluntly A true single source of truth never really happens in the real world, only in the slide decks of consultants. The goal is real, but it's a direction you keep moving toward, not a finish line. You cross once and forget. The mechanics are simpler than the philosophy. A semantic model can live in one workspace and be used by reports in another, so a central team can maintain the model while other teams build their own reports on top of it. You don't copy the data around you. Point every report at one shared model. That raises an obvious worry about who can build on your model and who decides which models are trustworthy. That's what governance means in plain words, and power BI handles it with a few small features.

11:57 The first is build permission. As the owner of a model, you decide who can build new content on top of it. That includes people building reports, and it includes people pulling the same numbers into Excel. Handing out build permission is how you share a model widely without handing over the keys to change it. The second is a pair of badges promoted and certified. A model creator can promote a model to make it easier to find, and can ask experts in the organization to certify it. Certification is the strongest signal, and it tells every report author that this model is official and high quality. When you open a workspace and see a certified model, you know you can build on it with confidence. The everyday version of why that matters looks like this. Without a shared certified model, five people build five reports and each one ends up using its own version of the revenue number.

12:40 The numbers drift apart. Nobody can explain why, and every meeting turns into an argument about whose spreadsheet is right. A certified shared model is how you end that argument before it starts. One question and then we move on. Sharing a model only works when people have access to it. If you build a report on a model that your audience can't reach, the report won't open for them. So access is part of the job, not an afterthought. So step back and look at what a semantic model really is. It's the layer where your raw tables become something a report can trust. It holds the tables, the relationships, and the measures. It decides whether your data stays fast, fresh, and consistent. And when you share it well, it becomes the reason your whole team finally looks at the same numbers. The quick recap. Here's the short version.

13:21 The semantic model is the layer between your data and your reports, where tables, relationships, and measures turn plain data into answers. The storage mode you choose decides how fast the numbers load and how fresh they stay, and a shared certified model is how a team stops rebuilding the same thing five times. Now, my question for you. If you looked at your own reports today, would you know which semantic model sits behind them, or would that layer be a mystery? Tell me in the comments, because your answer says a lot about how much of your reporting you really control.