If you want a Power BI report – a dashboard – or want to ask questions about your data by using Copilot or another data agent, then you need a Power BI semantic model. It sounds technical, but really, it’s just the data and calculations sitting behind your report. In a nutshell, a semantic model is the engine and the brain for analytics and AI; it’s just the setup of your data to reflect whatever business process you’re reporting on or analyzing.
Creating a semantic model can be quite intimidating, if it’s your first time. In this article, we want to walk you through the basic, most important steps, so you can get up and running quickly. This isn’t a comprehensive tutorial, but just a quick starting point and reference guide for each step, if you’re new to Power BI.
AI can help with parts of this workflow: Tabular Editor 3 has an AI assistant, the Tabular Editor CLI can be driven by coding agents, and TMDL files can be edited directly in VS Code. None of that replaces the fundamentals in this article. Use the tools to speed up careful work, not to skip understanding the model you are building.
You first need a clear understanding of what you are making, who will use it, and why. If you don’t, then you run the risk of making something that isn’t useful, which wastes resources. To avoid this, you should form a good plan and create a good design to make something that will be useful. This is critical irrespective of whether you will build yourself, or with AI assistance.
This first step is all about requirements gathering and design. If you’ve never done this before, then you can already go very far in a few steps:
This step is the most human part of the whole process, do not outsource it to AI. Requirements gathering is empathic and interpersonal work: you have to interview your stakeholders, watch how they actually use their current reports, and dig for the questions behind the questions.
Resist the temptation to drop a requirements document into your chatbot of choice and let it spit out a model design. The output may look convincing, but if the requirements were never truly captured, you’ll have built a dashboard that gets dropped like a rock the moment it’s delivered. AI can help you organize and sanity-check what you learn, but it can’t do the listening for you. For how to do this well, see We need this report in Power BI.
If you want more details about requirements gathering, we have some helpful links for you, here, each of which have more detailed steps, approaches, or even frameworks and templates to do this first step.
Garbage in, garbage out is an adage that remains true in the “era of AI”. In short, if your data has problems, then your models and reports will have problems. Therefore, before you start building anything, you need to explore your data and evaluate whether this is a possibility, or not.
This is quite simple: you need to spend some time browsing and evaluating your data to try and find any issues that could prevent you from doing a good job. It doesn’t matter if your data source is a single Excel file, or multiple enterprise data stores. Trust us, if you spend at least half a day doing this, it will save you a lot of time and headache down the line.
It doesn’t matter how you explore your data; you can do this in Power BI itself (either in Power Query before you load the data, or in the report canvas after you load it). This is also something that some AI tools can help with, if your organizations have access to ones that are safe to use for your security and privacy guidelines.
Here are a few things that you need to look for or think about:
At this point, you might want to transform your data. You can do this in Power BI by using Power Query, which is quite easy to use, and can be very powerful. However, if you are making a model from a Lakehouse that someone shared with you, then you might not be able to do this. If that is the case, then you will have to contact the person who owns the Lakehouse to request data changes or transformations.
In general, you should know that there are various tools that you can use to transform your data. In general, the guidelines are to use the right tool for the right problem, and transform data upstream if possible, and downstream if necessary:
If you are transforming data in Power Query, though, it helps to follow some tips:
AI can help here, too. In Fabric Dataflows Gen2, Copilot can explain what a query or step does in plain English and can turn a plain-language description into a custom column, so you learn what your transformations do instead of guessing. Tabular Editor’s AI Assistant can also profile your data for quality issues before they reach the model.
Fabric Data Apps are a new alternative to Power BI reports. Getting your data ready for those is arguably simpler than getting your data ready for reports, and we’ll publish an article on that topic soon. Read our blog on data apps for what they are and how they compare to reports.
Some helpful links for this step include:
If you have multiple tables, then you will have to figure out how to use them together. In other tools, you typically join the tables together or perform a LOOKUP to add column values from one table to another based on a common field. You can do these things in Power BI, but it is much easier to just link the tables with relationships.
Relationships let you group data from one table using columns from another table. You set up relationships when there are two columns with the same values on either table. Relationships are what enable Power BI to use multiple tables conveniently and performantly without having to smash them together during data transformation.
Power BI will automatically create relationships for you when you load tables for the first time (or when you refresh data, if you enable it in the settings). However, you will likely need to create or modify relationships, yourself, too. This is easy to do either from the model diagram view, or from the Manage relationships pane:
This is essentially the core of what data modelling is: choosing the tables and columns so that you can link them all together in way that is functional, makes sense, and gives you the result that you want. If you plan your tables and relationships well, then your model should somewhat resemble a star schema, where tables that have descriptive attributes (dimension tables, like product or customer) link tables that have quantitative attributes (fact tables, like sales or budget).
You don’t have to use a star schema model design. However, Power BI (and your reports and queries) typically performs best when you do. You can read more about the star schema below if you are interested or need more detailed help on the subject.
Some helpful resources for this step include:
If you have dates in your data that you will use in a report or in queries, then you likely need a date dimension table. A date table is a special table that contains all the dates from the earliest possible date in your model, to the latest possible date (or possibly future dates). If you’ve never heard this before, it might seem strange. However, this is an important part of a data model for the following reasons:
It does not matter how you create your date table, but most people prefer to do it in DAX. There are a variety of templates and tools that help you do this, so it is not something that you should spend too much time and effort on. Just make sure that you are using a date table and that it is configured properly in your Power BI semantic model.
If your organization uses a non-standard calendar (a fiscal year, retail 4-4-5 or ISO weeks), the new enhanced time intelligence (in preview) lets you define that calendar on your date table and use calendar-aware functions like TOTALWTD and STARTOFWEEK, instead of hand-writing fiscal-week DAX.
Some helpful links for this step include:
Chances are that you will need to aggregate data in your model to show a total sum, average, or something else. You can do this by selecting a column and grouping it in the visual, which is known as an implicit measure. However, implicit measures are unreliable and can cause problems in different ways.
Instead, you will probably need to use the Data Analysis eXpressions (DAX) language to specify your calculations in DAX measures. Measures are just calculations that you organize in tables and use in visuals to group data. If you are completely new to Power BI and do not have a technical background, then you will likely struggle the most with DAX. However, there are a lot of AI tools which can help you write DAX now; although, when you use AI to generate code, we do recommend the following:
User-defined functions (UDFs) are useful when a calculation pattern should be reused. You define the logic once with parameters, then call the function from measures that pass in the relevant model values. You can read our blog on UDFs in simple terms to get started.
For example, a reusable comparison function can calculate the relative difference between an actual value and a target value:
Comparison.RelativeToTarget =
(
actual : SCALAR NUMERIC VAL,
target : SCALAR NUMERIC VAL
) =>
VAR _Actual = actual
VAR _Target = target
VAR _Delta = _Actual - _Target
VAR _Percentage = DIVIDE ( _Delta, _Target )
RETURN _Percentage
Actuals vs. Budget MTD =
Comparison.RelativeToTarget (
[Actuals MTD],
[Budget MTD]
)
The measure stays short, and the comparison logic lives in one place. This is easier to maintain than copying the same percentage-difference logic into multiple measures.
If you would rather not write functions from scratch, the DAX Package Manager installs ready-made, community-maintained ones from daxlib.org in a few clicks. For common report calculations like a running total or percent-of-total, visual calculations are often the easiest route and need no model measure at all. Copilot in the DAX query view can also generate and explain DAX, but you should still check its output.
Note that you might also have to set up specific tables, relationships, and DAX for data security, which is a separate and complex topic that goes beyond the scope of this article… but it might be something that you need to consider, even for a first model.
Some helpful resources for this step include:
It might seem obvious, but you need to test your model thoroughly as you build it before you start to create your reports, agents, or share it with others. This requires a good, critical, and inquisitive mindset. Honestly, one of the best ways to do this is to just throw a bunch of visuals on the report canvas, and filter everything a variety of different ways. Then, look to see if there are any unexpected results, blanks, or other issues.
Once you identify and fix the biggest problems, then you can continue testing as you build reports. If you are only building AI agents or plan to share your model directly with others, then you might want to extend and deepen your visual-based testing, instead. Regardless, you need to really think like a business user as you test; see the data from their point of view.
Some things to look out for as you test include:
You don’t have to check everything by hand. Tabular Editor’s Best Practice Analyzer scans your model against a community-maintained ruleset and flags issues like missing relationships, risky DAX, or naming problems, which is a fast way to sanity-check a first model. You can also add your organization’s custom rules to, for example enforce a specific naming convention.
Some helpful resources for this step include:
Once you create a model, you probably need to publish and share it (or the reports or agents that use it). Publishing and sharing a model seems like something so simple (just two buttons, right?). However, it is actually one of the more complex parts of creating a model, because there are so many possible options which determine who you can share with and what they can do with it.
The following is an overview of the options from the Microsoft documentation:
Diagram taken from Microsoft documentation: Content Distribution and Sharing.
In general, you want to ensure that you publish your model to a Fabric or Power BI workspace, rather than sharing .pbix files with other people. Then, you can connect to that shared semantic model from other items and re-use it.
To distribute a finished report set, the recommended approach is an org app: you bundle reports into an app and use audiences to show different content to different groups, and granting someone access to the app automatically grants them read access to the underlying semantic models.
Some helpful resources for this step include:
Creating a semantic model is complex. There’s a lot of steps, and we’re just covering some of the most important ones, here. If you want to learn more, please check out our other blog articles, and our free video trainings that can help you get started.