How to Create a Cube in Power BI

Trying to make sense of your data in Power BI often feels like you have a dozen separate puzzles instead of one complete picture. You have your sales data in one table, customer information in another, and product details somewhere else. Creating a simple chart showing revenue by customer region suddenly becomes a major headache. The solution lies in building a "cube," or more accurately, a proper data model.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

This article will show you exactly how to structure your data in Power BI by building a data model. We'll walk through a step-by-step process that turns your disconnected tables into a powerful, interactive analytical engine, making your reporting faster, easier, and far more insightful.

What Exactly Is a "Cube" in Power BI?

First, let's clear up some terminology. The term "cube" comes from older BI technologies like SQL Server Analysis Services (SSAS), where data was literally structured in multi-dimensional cubes (think of a Rubik's Cube where each small square holds a piece of data). While Power BI can connect to these traditional cubes, when most people talk about creating a "cube" within Power BI, they are really talking about building a tabular data model.

Think of it like organizing a library. Your raw data files (Excel sheets, database tables) are like piles of unsorted books on the floor. A Power BI data model is the card catalog and shelving system. It doesn't change the content of the books, but it creates clear relationships between them so you can instantly find exactly what you need - for instance, "all books on marketing written in the last five years."

In short, a Power BI data model (our "cube") connects all your separate data tables together logically, making it the foundation for every report and dashboard you build.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Why You Need a Proper Data Model

Jumping straight into building visuals with messy, disconnected data is a recipe for frustration. Taking the time to build a solid model first provides several massive advantages:

• Speed and Performance: A well-structured model allows Power BI's engine (the VertiPaq engine) to compress data and perform calculations with incredible speed. Queries that might take minutes on raw data can run in seconds.
• Single Source of Truth: By defining calculations and business logic inside the model, everyone on your team uses the same definitions. No more arguments over which spreadsheet has the "correct" revenue number.
• Simplified Reporting: For the people building reports, the experience becomes drag-and-drop simple. All the complexity is handled in the model, so creating a new chart doesn't require complex formulas or data wrangling.
• Powerful Analytics: A good model unlocks advanced features, especially time intelligence. Calculating things like year-over-year growth or moving averages becomes straightforward once you have a proper date table set up.

Step-by-Step: How to Build Your Power BI Cube (Data Model)

Let's walk through an example. Imagine you run an online T-shirt store. You have three separate data sources:

1. An Excel file with daily sales transactions.
2. A database table of your products (SKU, name, color, price).
3. A CRM export with your customer list (customer ID, name, city, state).

Our goal is to create a model that connects these so we can analyze sales by product, customer location, and time.

Step 1: Get Data into Power BI Desktop

First, you need to bring your data into Power BI Desktop. Open Power BI Desktop and on the Home ribbon, click on Get Data. Connect to each of your sources one by one (e.g., "Excel Workbook" for sales, "SQL Server" for a database, etc.).

When you connect, Power BI will show you a preview of the tables. Select the ones you need and click Transform Data. Do not click "Load" yet. This takes you to the Power Query Editor, which is where the essential cleaning process happens.

Step 2: Clean and Transform Your Data in Power Query

Your model is only as good as the data you put into it. Power Query is your workshop for shaping the data before you start modeling. Here are a few critical tasks:

• Remove unnecessary columns: If your customer table has columns you'll never use for analysis (like 'last_login_ip'), remove them. A leaner model is a faster model.
• Check and set data types: Make sure dates are recognized as dates, numbers as numbers (e.g., "Decimal Number"), and text as text. Power BI is smart about this, but it's good practice to verify.
• Rename columns and tables: Give your tables and columns clean, user-friendly names (e.g., rename 'cust_ID' to 'Customer ID'). This makes reporting much more intuitive later.
• Filter out irrelevant rows: If you have test orders or internal employee purchases in your sales data, filter them out.

Once you are happy with the state of each table, click Close & Apply on the Home ribbon. Power BI will now load the cleaned data into your model.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 3: Build the Model with the Star Schema

This is the core of creating your "cube." We’ll organize our tables into a star schema. It sounds technical, but the concept is simple. It consists of two types of tables:

• Fact Tables: These tables contain numeric, transactional data - the "what happened." In our example, the Sales table is our fact table. It has metrics like Order Quantity and Total Revenue, along with keys that link to our other tables (like Product ID and Customer ID).
• Dimension Tables: These tables contain descriptive, categorical context - the "who, what, where, when." Our Customers, Products, and a dedicated Date table are our dimensions. They describe the facts.

Now, go to the Model view in Power BI (the third icon on the left-hand pane). Here you'll see your tables represented visually.

Power BI will often automatically detect and create relationships if your column names match (e.g., if both tables have a 'Product ID' column). If not, you can create them manually by simply clicking and dragging the key column from one table to the matching column in another.

For our example:

1. Drag Product ID from the Products table to Product ID in the Sales table.
2. Drag Customer ID from the Customers table to Customer ID in the Sales table.

You’ll see lines appear connecting your "dimension" tables to your central "fact" table, looking like a star - hence the name!

Pro-Tip: Create a Dedicated Date Table

While your sales table has a date column, you should always create a dedicated calendar or date table. This is the key to unlocking powerful time-intelligence analysis. You can create one easily in Power BI using DAX.

Go to the Data view, and on the Table Tools ribbon, click New Table. Enter this simple DAX formula:

Dates = CALENDARAUTO()

This creates a table with a single column containing all dates found in your model. You can then add more columns for year, quarter, month name, etc. Once created, connect this new 'Dates' table to the date column in your 'Sales' table in the Model view.

Step 4: Add Business Logic with DAX Measures

Now that your tables are connected, you need to create the calculations that will power your reports. These are called measures, and you write them using the DAX (Data Analysis Expressions) language. It’s like Excel formulas, but supercharged for data models.

Instead of manually summing up revenue in a visual, we create a measure that can be reused anywhere. Go to the Report view, right-click on your Sales table, and select New Measure.

Here are a couple of essential measures to start with:

Total Sales:

Total Sales = SUM(Sales[Revenue])

Total Orders:

Total Orders = DISTINCTCOUNT(Sales[Order ID])

By creating a measure, you are standardizing this calculation. Anyone using this model who drags Total Sales into a report will get the exact same number, calculated the same way. This is your single source of truth.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 5: Visualize Your Data

This is where all your hard work pays off. Go to the Report view and look at your Fields pane on the right. You'll see your organized tables and the measures you created (they have a little calculator icon).

Now, creating visuals is incredibly easy:

• Want to see sales by customer state? Grab the 'State' field from the Customers table and the Total Sales measure you created. Power BI will instantly generate a map or chart.
• How about sales by T-shirt color over time? Drag 'Month Name' from your Dates table, 'Color' from the Products table, and 'Total Sales' onto your report canvas.

Because you built the model, you're no longer limited to data from a single table. You can slice and dice your facts (sales) by any of your dimensions (customers, products, dates) seamlessly.

Data Model Best Practices

As you get more comfortable, keep these tips in mind to build efficient and user-friendly models:

• Hide foreign keys: In the model view, hide the key columns (like 'Customer ID') in the fact table. This prevents report builders from accidentally using them instead of the dimension columns, preventing confusion.
• Use clean names: Give your measures clear, space-separated names (e.g., "Year Over Year Sales Growth").
• Avoid bi-directional relationships: Power BI sometimes creates relationships that filter in both directions. Unless you have a specific reason, stick to single-direction relationships (from the dimension table to the fact table) for better predictability and performance.
• Optimize column types: Use whole numbers instead of decimals when possible, as they compress better and perform faster.

Final Thoughts

Mastering the data model in Power BI unlocks its true analytical power. By taking the time to properly clean your data in Power Query, building a star schema to define relationships, and centralizing your business logic with DAX measures, you create an efficient and reliable foundation for all your present and future reports.

This whole process can still feel overwhelming, especially if data modeling and DAX are new to you. Building these models takes time and a steep learning curve. That’s exactly why we built Graphed. We wanted to skip that entire manual process. Instead of spending hours learning Power BI, you can connect your data sources like Shopify or Google Ads, and then just ask for what you want in plain English, like "Show me my total sales broken down by city for the last quarter." We handle connecting the data, building the model, and generating the visual for you in seconds, so you can focus on insights instead of configuration.