In most of the courses, you used dimensions from filter tables to make a table visual and started talking about “in this context,…”. What if this dimension is from a fact table, is it still considered the same context? For example, I have a product category filter table but in my fact table there is also a product category column. What difference will it make to context and further DAX calculations between using the Product Category from filter table to create a visual and using Product Category column from fact table to create a visual?
You want to use the columns from your Dimension tables to filter your fact tables because your Dimension tables should be exponentially smaller than your fact tables. So when you filter, say, product category you want DAX to “start” that filter in the dimension table and then send that filtered table to your fact table to some sort of aggregation. You really dont want to filter the Fact table directly since it would be such a large table, which translates into a slow user experience.
That is why it’s generally a good idea to just have product key in your fact table and relate that to your dimension table. It also allows for you to filter on any column in the product table (say product color). and all you need is just a relationship between the dimension and fact table.
Nick
Yes agree with everything Nick has said here.
The reality though is when context is referred to, it doesn’t matter where the dimension comes from. It could be from either a lookup or fact table.
Best practice though is to get your filters/dimensions situated into lookup tables. That is the best way to model your data in Power BI.