I have written previously about what is a fact table, and what is the dimension table, and how they relate to each other in a Power BI data model. However, still I see when people use these tables in their model, they get ambiguity of what table should be used for what, and if they bring a count of a field from one table, why it doesn’t work, whereas compared to another table it works. How to find the right table to get your values from and get that showing in Power BI visual correctly. In this blog article, I’m going to show you that. If you like to learn more about Power BI, read Power BI from Rookie to Rock Star book.
Sample Model
The sample model that I use here is coming from the AdventureWorksDW2012 Excel file, which you can download it here.
I have a few tables in my model as below: Tables are DimCustomer, FactInternetSales, DimDate, and DimProduct.
Values that Exists Only in One Table
When you get a value which you can only fetch from one table, there is no problem. Example: SalesAmount field exists only in the FactInternetSales table. So in visualization, I can easily build a report like this:
Even if I want count of orders, still because that should come from the table that includes order details (which in this case would the FactInternetSales) still it is easy. I just drag a field (no matter which one, because the count of any of the fields in the same table is always the same), and then change the aggregation to count, and it works perfectly fine:
Both visualizations above are showing slicing and dicing the data of orders (one is the SalesAmount and the other one count of orders) by the EnglishEduction which is from the DimCustomer table.
Values that can be fetched from Multiple Tables
The problem happens, when there is a value that you can fetch from multiple tables. Let’s talk about one scenario as an example:
We have FullName (from DimCustomer table) of customers in the table below, and we want to add the count of products that have purchased besides them as a value column.
In a scenario like this, one of the very first actions for many developers is to go and search for something about Product in the fields pane.
The product search will bring a lot of fields if we narrow it down with using ProductKey, we see that is available in two tables: FactInternetSales and DimProduct.
In a properly designed data model, the ProductKey has to be hidden, because it is a technical column, I have explained about that best practice in modelling here, but for this example, let’s keep things simple as is.
If you use the ProductKey from the DimProduct table, then you get this output:
If you wonder, why all count of ProductKey values in the visual above is 606, I have explained it fully in details in another article about the direction of the relationship in Power BI. As you see in the model diagram below, the DimCustomer table cannot filter the value in the DimProduct table, because the single-directional relationship between the DimProduct and FactInternetSales table.
You might then think of changing the relationship to both-directional between the DimProduct and FactInternetSales table which is big modelling mistake. I have explained in another article how tables should build a star schema with a single-directional relationship from all dimension tables to the fact table. Your model should always look like this:
So, How should you achieve that? well, by choosing the value from the correct data table; FactInternetSales table.
If I do the same with getting the value from the FactInternetSales table, then we get the correct value;
Depends on the logic of your calculation, you might need to get Count of that field, or Count (Distinct) of that (because there are duplicate ProductKey values in the FactInternetSales table; a product can be sold multiple times of course). As you see in the above visualization, the value is shown correctly.
How Do I Know Which Table Should I Get the Value from?
Well, we solved the above scenario. However, in a more complicated model, where you have more than two or three tables, finding the right table to get the value from, can be a challenge. Here is the golden rule to select the values:
Any field that is going to be used as a slicer, filter, Axis of a chart, headers of rows or columns in a table or Matrix visual, should come from Dimension tables.
Examples of these fields are FullName of the customer. EnglishEduction of the customer. Date of the order, Product name, Product colour, Age of the customer. As you can see some of these are even number (i.e. age), but because they have used for slicing and dicing, they should come from a Dimension table. If you don’t know what the dimension table is, read this article.
In the above visuals, BirthDate and YearlyIncome are coming from their Dimension tables.
Any field that is going to be used as a value of a chart, table or Matrix visual, should come from Fact tables.
Examples of these values are; SalesAmount, Order Quantity, Count of Products been sold, Count of Customers purchased specific products, etc. If you don’t know what is the Fact table, read my article here about it.
The Count of ProductKey from the FactInternetSales table that you see above was one of the examples;
What If I don’t have Fact or Dimension tables designed property?
At this point, you might say that it is easy in a proper star-schema design where there are fact and dimension tables designed properly. However, in my model, everything is messed up. What should I do with that? That is a great question. My answer is this: Having a proper Power BI model design is the building block of all other steps afterwards. If you don’t have the right data model, everything is going to be messed up on top of it. You cannot build a house on top of a building block which is not solid itself. I’d say, whatever stage or step of the Power BI development you are; consider re-designing your model seriously. There are simple things that you can do to change your model to a good data model, it would take time, but it would well worth it. I explained one example of how to build a simple proper data model here.
Summary
Values in your visuals should come from Fact tables, and slicers/filters/axis/column or row headers should come from Dimension tables. For that, you do need to have proper dimension and fact table design. However, that is not hard to implement. Is there a problem you cannot get solved in your data model using these tips? let me know down in the comments below for free questions and answers, or use the Contact Us button in our website to set up a consulting time with me to discuss that.
I highly recommend reading my other articles about the basics of data modelling in Power BI;
- What is the Relationship in Power BI?
- What is the Cardinality of the Relationship?
- What is the Direction of the Relationship?
- Data preparation; First and Foremost Important task
- What is a Dimension table and why say No to a single big table
- Basics of Modeling in Power BI: Fact Tables
- Combining Dimension Tables in Power BI using Power Query; Foundation of Modeling in Power BI
- Star Schema and How to Build It
- Creating a Shared Dimension Using Power Query and Power BI
- Build Your First Star Schema Model in Action
- Budget vs. Actual: Zero Complexity model in Power BI
- Budget vs. Actual: Calculations in DAX
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
I’m curios about why everyone is always promoting the dimensional model (star schemes) when it has so many flaws? The major flaw is of course that such model can’t answer many important questions. Eg it can’t answer these two questions “average age for customers making $100 purchases” and “average sales amount for customers aged 25”. Another problem is that dimensions can’t filter each other so when you choose a specific year you still see customers for the other years. Other models, such as KeyTable, can handle these problems. Would be interesting to hear your take on this!
Hi Lars
All of these questions you mentioned are sample of measures which can be written simply in DAX. the model designed in a star schema would be able to answer that using those DAX measures. I am not saying that if you use star-schema, then you don’t need DAX. you would need DAX anyways, but complication of that would be much less, and the performance much higher. at the end of the day, it is all about the performance further along the road.
Cheers
Reza
Hi,
Thank you very much for this article.
We can also use the function CROSSFILTER especially if we want to count items that there are not into the fact table (for example ProductSubcategory)
Hi Hoss
Yes, we can use CROSSFILTER function in DAX, however, that will make the relationship both directional which cause performance issues. the best is always to get values from the fact table anyways.
Cheers
Reza