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.
YEAR OVER YAER OF GRANDTOTAL SPOTFIRE 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.
YEAR OVER YAER OF GRANDTOTAL SPOTFIRE 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.
0 kommentar(er)
