A Data model in Power BI consists of the right number of tables with the right relationship between these tables. The data is present in the table but can be accessed by other tables only when a correct relationship exists between two tables. In this blog post, a fictitious data set of various companies selling mobile phones is used to answer the following business questions:
- What is the total Sale Quantity for Apple?
- What is the total Sale Quantity for Apple in Australia in Year 2015 and 2016?
In the first part of the blog, the concept of filter propagation in Power BI has been discussed. This part also covers the mechanism behind the filter propagation and demonstrate how multiple filters work together. In the later half, we have answered the business questions listed above using the concept of filter propagation.
Let’s walk along the path a filter takes in a model
The Sales table in this Data set is a Fact table
surrounded by multiple Dimensional tables
If you’re new to this concept, here’s a complete guide to what fact and dimension tables are: https://powerbitraining.com.au/what-is-a-data-model-in-power-bi/
The Filters can propagate from the 1 side of a relationship. The screen shot below show the direction in which the data is filtered. The filters start propagating from the dimension table and end at the fact table.
To answer this question, the filters start propagation form apple in the Manufacturing table and end up by listing rows containing 1 as the primary key in the Products Table. The filter then propagates from the Products table to the Sales table and lists down the rows containing 2 as the Product key. Finally, a summation DAX function is performed to find the quantity of Apple Products sold.
To answer this question in Microsoft Power BI:
- Click on a card visual from the Visualization Pane and drag and drop the Quantity Sold from the Sales table to Fields.
- Now click on slicer in the Visualization Pane and Drag and drop the column Manuf from the Manufacturer table to the Fields.
- Click on Apple in the Slicer.
Along with the filters filtering Apple now you have filter propagation starting from the country table and another filter propagation starting from the year table. The result only contains values from the intersection of all three filters.
To answer this Question in Power BI:
- Click on a card visual from the Visualization Pane, drag and drop the Quantity Sold from the Sales table to Fields.
- Now click on slicer in the Visualization Pane, drag and drop the column Manuf from the Manufacturer table to the Fields. Click on Apple in the Slicer.
- Click on slicer in the Visualization Pane, drag and drop the column Year from the Year table to the Fields. Type 2016 in the text cursor area at the end of range.
- Click on slicer in the Visualization Pane, drag and drop the column Country from the Country table to the Fields. Click on Australia.
Conclusion:
Data Modeling is an important aspect of data analysis. A good data model contains the right number of tables with the right relationship between them. Setting the correct relationship between the tables in data model is important because the relationship decides the direction of filter propagation in Power BI. In this blog post, the mechanism of filter propagation in Power BI has been discussed using a fictitious data set and a few business questions have been answered using the correct filter propagation.
Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.
More Checkouts for Power BI Training in Australia
