- NOT ONLY IS THIS NOT CORRECT IT IS NOT EVEN WRONG HOW TO
- NOT ONLY IS THIS NOT CORRECT IT IS NOT EVEN WRONG DOWNLOAD
As you can see some of these are even number (i.e. Date of the order, Product name, Product colour, Age of the customer. Here is the golden rule to select the values:Īny 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.Įxamples of these fields are FullName of the customer. 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. How Do I Know Which Table Should I Get the Value from?
As you see in the above visualization, the value is shown correctly. If I do the same with getting the value from the FactInternetSales table, then we get the correct value ĭepends 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). So, How should you achieve that? well, by choosing the value from the correct data table FactInternetSales table. 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. You might then think of changing the relationship to both-directional between the DimProduct and FactInternetSales table which is big modelling mistake. 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. If you use the ProductKey from the DimProduct table, then you get this output: 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. 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 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. 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. Let’s talk about one scenario as an example: The problem happens, when there is a value that you can fetch from multiple tables. Values that can be fetched from Multiple Tables 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:īoth 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. So in visualization, I can easily build a report like this:Įven 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. Example: SalesAmount field exists only in the FactInternetSales table. When you get a value which you can only fetch from one table, there is no problem. I have a few tables in my model as below: Tables are DimCustomer, FactInternetSales, DimDate, and DimProduct.
NOT ONLY IS THIS NOT CORRECT IT IS NOT EVEN WRONG DOWNLOAD
The sample model that I use here is coming from the AdventureWorksDW2012 Excel file, which you can download it here. If you like to learn more about Power BI, read Power BI from Rookie to Rock Star book. In this blog article, I’m going to show you that.
NOT ONLY IS THIS NOT CORRECT IT IS NOT EVEN WRONG HOW TO
How to find the right table to get your values from and get that showing in Power BI visual correctly. 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.
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.