保留正文与补采附件。源页面当前未重新核验；具体限制见交付清单。

Revisit consent button We value your privacy We use cookies to enhance your browsing experience, serve personalised ads or content, and analyse our traffic. By clicking "Accept All", you consent to our use of cookies. Customise Reject All Accept All Customise Consent Preferences We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below. The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... Show more NecessaryAlways Active Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

Functional Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

Analytics Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

Performance Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

Advertisement Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

Uncategorised Other uncategorised cookies are those that are being analysed and have not been classified into a category as yet. No cookies to display. Reject All Save My Preferences Accept All Toggle SearchToggle Mobile Navigation Publish AI, ML & data-science insights to a global community of data professionals. Sign inSubmit an Article LatestEditor's PicksNewsletterShipAI

Write For TDS LinkedInXToggle Search Data Science

Beyond the Flat Table: Building an Enterprise-Grade Financial Model in Power BI

A step-by-step journey through data transformation, star schema modeling, and DAX variance analysis with lessons learned along the way. Ibrahim Salami January 10, 202610 min read Created with Gemini We’ve all been there: You open Power BI, drag a messy Excel sheet into the canvas, and start dropping charts until something looks “right.” It’s easy, it’s intuitive, and honestly, that’s why Power BI is one of my favourite tools for data visualisation. But as the world of data shifts toward end-to-end solutions like Microsoft Fabric , “just making it work” isn’t enough anymore. Large organisations need models that are performant, secure, and scalable. I’ve decided to challenge myself by taking the PL-300: Microsoft Data Analyst Associate exam. But instead of just grinding through practice tests or memorising definitions, I’m going into “Practical Mode.” If I’m going to get certified, I want to prove I can solve the problems real businesses actually face.

The Mission: The Enterprise-Grade Financial Suite

For my first project, I’m tackling the Executive Financial Health Suite. Learn this step by step with the interactive AI and Data Scientist roadmap. Why finance? Because in the enterprise world, it’s the ultimate test of your Data Modeling and DAX skills. Most “generic” tutorials use a single, flat table. But in a real company, data is fragmented. You have “Actuals” (what happened) sitting in one place and “Budgets” (the goal) sitting in another, usually at different levels of detail. In this project, I’m going to document how I:

I’m sharing my journey in public so that if you’re also preparing for the PL-300, you can follow along, build these solutions with me, and understand the why behind the architecture — not just the how.
For this project, we are using the Microsoft Financial Sample. It’s the perfect “blank canvas” because it comes as a flat, “messy” table that we have to re-engineer professionally.

How to get it: In Power BI Desktop, go to Home > Sample Dataset > Load Sample Data. Select the financials table. Let’s get our hands dirty in Power Query.

Phase 1: Data Transformation (Power Query)

Before touching DAX or visuals, I slowed myself down and spent real time in Power Query. This is the part I used to rush through. Now I treat it as the foundation of everything that follows.
If the data model is shaky, no amount of clever DAX will save you.

Step 1: Data Profiling (a quick reality check)

Once I loaded the Microsoft Financial Sample dataset, the first thing I did was turn on column profiling :

When I turn on Column quality, distribution, and profile , I’m not trying to be thorough for the sake of it. I’m scanning for model-breaking issues before they turn into DAX headaches. Column profiling immediately tells you:

1. Nulls & Data Type Mismatches I think we’re good. Empty values are 0% all through, valid are 100%, and errors are 0%. Data types are all good, also. Probably because we’re using the sample financials dataset, there shouldn’t be any issues 2. Cardinality: What Wants to Be a Dimension Cardinality is simply how many unique values a column has. Power BI surfaces this immediately in Column distribution , and once you start paying attention to it, modeling decisions get much easier. Here’s my rule of thumb:

When I turn on column distribution, I’m asking two questions:

If a column looks categorical but has thousands of distinct values, that’s a red flag. Once I turned on Column distribution , the dataset started sorting itself for me. Some columns immediately showed low cardinality — they repeated often and behaved like true categories:

These columns had relatively few distinct values and clear repetition across rows. That’s a strong signal: these want to be used for grouping, slicing, and relationships. In other words, they naturally belong on the dimension side of a star schema. Then there were the columns on the other end of the spectrum. Measures like:

...showed very high cardinality. Many values were unique or nearly unique per row, with wide numeric ranges. That’s exactly what I expect from fact-level metrics — they’re meant to be aggregated, not filtered on. That insight directly informed my next step: using Reference in Power Query to spin off Dim_Product and Dim_Geography, instead of guessing or forcing the structure.

Step 2: Spinning Dimensions with Reference (Not Duplicate)

This is the point where I stopped treating the dataset as a report-ready table and started treating it as a model-in-progress.
In Power Query, it’s tempting to right-click a table and hit Duplicate. I used to do that all the time. It works — but it quietly creates problems you only feel later. Instead, I used Reference.
Why reference instead of duplicate? You might ask When you create a referenced table:

From a real-world perspective, it’s just… safer. Here’s how I created Dim_Product & Dim_Geography Starting from the main financial table:

What I ended up with was a small, stable table with low cardinality — perfect for slicing and grouping. I repeated the same approach for geography:

P.S. In this dataset, geography is represented only at the country level. Rather than forcing a region or city hierarchy that doesn’t exist, I modeled Country as a lean, single-column dimension.

Step 3: Create a Dynamic Date Table

Here’s where I see a lot of Power BI models quietly fail PL-300 standards.

Why this matters:

To create a dynamic date table. Just click on Load - > Blank Query -> Advanced Editor and paste this code in Below is the exact M code I used powerquery

let
Source = Financials,
MinDate = Date.From(List.Min(Source[Date])),
MaxDate = Date.From(List.Max(Source[Date])),
DateList = List.Dates(
MinDate,
Duration.Days(MaxDate — MinDate) + 1,
#duration(1, 0, 0, 0)
),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}),
AddYear = Table.AddColumn(DateTable, “Year”, each Date.Year([Date]), Int64.Type),
AddMonthNum = Table.AddColumn(AddYear, “Month Number”, each Date.Month([Date]), Int64.Type),
AddMonthName = Table.AddColumn(AddMonthNum, “Month Name”, each Date.MonthName([Date]), type text),
AddQuarter = Table.AddColumn(AddMonthName, “Quarter”, each “Q” & Number.ToText(Date.QuarterOfYear([Date])), type text),
AddDay = Table.AddColumn(AddQuarter, “Day”, each Date.Day([Date]), Int64.Type)
in
AddDay

This calendar:

Once loaded, I marked it as a Date Table in the model view — non-negotiable for PL-300. By the end of Phase 1, I had:

Nothing flashy yet — but this is the phase that makes everything after it easier, faster, and more reliable. In the next section, I’ll move into data modeling and relationships , where this structure really starts paying dividends.

Phase 2: Data Modeling (From Tables to a Star Schema)

This is the phase where Power BI starts behaving like a semantic model. By the time I switched to the Model view , I already had:

Now the goal was simple: connect everything cleanly and intentionally.

Step 1: Establishing the Star Schema

I aimed for a classic star schema :

Every relationship needed to answer three questions:

You might notice that I didn’t introduce surrogate keys for the fact or dimension tables. In this dataset, the natural keys — Country, Product, and Date — are stable, low-cardinality, and unambiguous. For this model, adding artificial IDs would increase complexity without improving clarity or performance. Here’s how the overall model looks:

Step 2: Relationship Direction (Single, on Purpose)

All relationships were set to:

For PL-300 and real-world models alike, single-direction filters are the default until there’s a strong reason not to use them.

Step 3: Date Table as the Anchor

The dynamic date table I created earlier now became the backbone of the model. I:

This does three important things:

From here on out, every time-based calculation flows through this table — no exceptions.

Step 4: Hiding What Users Don’t Need

This is a small step with an outsized impact. PL-300 explicitly tests this idea that the model shouldn't be just correct — it should be usable. I hid:

Step 5: Validating the Model (Before Writing DAX)

Before touching any measures, I did a quick sanity check:

If something breaks here, it’s a modeling issue — not a DAX issue. To test this, I created a quick visual checking the Sum of Profit by Year. Here’s how it turned out: So far so good! Now we can move on to creating our DAX measures.

Phase 3: DAX Measures & Variance Analysis (Where the Model Starts to Shine)

This is the phase where the work I’d done in Power Query and the model really started paying off. Honestly, it’s the first time in a while that writing DAX didn’t feel like fighting the table. The star schema made everything… predictable.

Step 1: Base Measures — the foundation of sanity

I resisted my old instinct to drag raw columns into visuals. Instead, I created explicit measures for everything I cared about: powerquery

Total Sales :=
SUM ( financials[ Sales])

Total Profit :=
SUM ( financials[Profit] )

Total Units Sold :=
SUM ( financials[Units Sold] )

Total COGS :=
SUM ( financials[COGS])

Step 2: Time intelligence without surprises

Because I already had a complete, properly marked date table, things like year-to-date or prior year comparisons were effortless. Sales Year-to-Date powerquery

Sales YTD :=
TOTALYTD (
[Total Sales],
‘Dim_Date’[Date]
)

Sales Prior Year powerquery

Sales PY :=
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( ‘Dim_Date’[Date] )
)

Step 3: Variance measures — turning numbers into insight

Once I had Actual vs Prior Period, I could calculate variance with almost no extra effort: powerquery

Sales YoY Variance :=
[Total Sales] — [Sales PY]

Sales YoY % :=
DIVIDE (
[Sales YoY Variance],
[Sales PY]
)

Same approach for month-over-month: powerquery

Sales PM :=
CALCULATE (
[Total Sales],
DATEADD ( 'Dim_Date'[Date], -1, MONTH )
)

Sales MoM Variance :=
[Total Sales] - [Sales PM]

Sales MoM % :=
DIVIDE (
[Sales MoM Variance],
[Sales PM]
)

Step 4: Why this actually feels “easy”

Here’s the honest part: writing DAX didn’t feel like the hardest thing. The hard part was everything that came before :

By the time I got here, the DAX was just adding value instead of patching holes.

Good DAX isn’t clever — it’s predictable, trustworthy, and easy to explain.

Conclusion

The magic wasn’t in any single DAX formula — it was in how the model came together. By profiling the data early, understanding cardinality, and spinning dimensions with Reference , I built a structure that just works. A dynamic date table and clean relationships meant time intelligence measures and variance calculations ran effortlessly. Hiding unnecessary fields and grouping measures thoughtfully made the model approachable, even for someone else exploring it for the first time. By the time I wrote the DAX for Actual vs. Prior Period or Month-over-Month variance, everything felt predictable and trustworthy. If you want to see the full semantic model in action, including all the tables, relationships, and measures, you can download ithere and explore how it ties together. There’s no better way to learn than seeing a working model in Power BI and experimenting with it yourself. Wanna connect? Feel free to say hi on any of the platforms below Medium LinkedIn Twitter YouTube

Register Now to Continue Reading

Unlock Towards Data Science with a Free Account

All articles on Towards Data Science are available to registered users. Create a free account today to access this article and thousands more. Register for freeSign in to your account

Written By

Ibrahim Salami

See all from Ibrahim Salami Busines IntelligenceData AnalyticsData ModelingData VisualizationPower Bi Share This Article

Towards Data Science is a community publication. Submit your insights to reach our global audience and earn through the TDS Author Payment Program. Write for TDS

Related Articles

SQL to SARIMAX: How I navigate the first time-series analysis personal project for my portfolio

Data Science Including the resources I used to learn time-series as I was doing this project (MySQL, ARIMA orders selection, parallelization in Jupyter... Irene Chang April 6, 202210 min read

Data Stacks For Fun & Nonprofit - Part II

Data Science Designing a complete data stack with Meltano, DBT and Superset. Andrew Stewart September 27, 202011 min read

Ethics in AI: Potential Root Causes for Biased Algorithms

Data Science An alternative approach to understanding bias in data Jonas Dieckmann January 27, 202310 min read

The Price is Too Damn High: Part 1

Data Science A look at submitted Medicare charges by state in 2017 and 2012 Anne Bode November 19, 202013 min read

Analysis with Pandas, Matplotlib, and Folium -Spanish courses for foreigners in the Municipal...

Data Science Side-by-side bar charts, stacked bar plots, pie plots, and cloropleth maps Amanda Iglesias Moreno February 5, 20204 min read

20 NumPy Operations That Every Data Scientist Should Know

Artificial Intelligence NumPy forms the basis of many Python libraries in the data science domain. Soner Yıldırım August 29, 20205 min read

Data Analyst's Primer to Slowly Changing Dimensions

Data Analyst interviews will typically entail both technical and behavioral components. Nick Vega April 25, 20218 min read

Aesthetic Epistemology: A Review of Erna Fiorentini's"Inducing Visibilities..."

Data Science A Brief Review of "Inducing visibilities: An attempt at Santiago Ramón y Cajal's aesthetic epistemology"... Jesse Ruiz (she/they) May 24, 20213 min read

Product Quasi-Experimentation: Statistical Techniques When Standard A/B Testing Is Not Possible

Data Science A guide to the most popular techniques when randomized A/B testing is not possible Magda Ntetsika July 19, 20246 min read

Your home for data science and AI. The world's leading publication for data science, data analytics, data engineering, machine learning, and artificial intelligence professionals. Subscribe to Our NewsletterWrite For TDS•About•Advertise•Privacy Policy•Terms of Use © Insight Media Group, LLC 2026•The New Stack•roadmap.sh•Creator Network

Recommended Articles

Close