We all like things that are automatically done for us but there are implicit actions in Power BI that you should avoid. These implicit actions are done within Power BI by default and it’s not always a good thing. Let me tell you why.
An example of one of these actions is it gives us implicit measures. These implicit measures are for things like auto aggregated values, such as if you have a sales column in a data table, that column would be automatically aggregated or summed up.
You want to use implicit actions as little as possible. What you want to do is to create explicit actions by creating new calculated measures. This way this measure you’re creating is always going to be an addition or an average, a min or a max or an account.
Another action that happens for us implicitly is relationships. Using these implicit relationships is fine, but we always want to double check to be sure they are behaving exactly as we intend. Speaking of relationships, there is something called bi-directional relationships in your data model. This is not an implicit action by and of itself as it must be enabled.
A bi-directional relationship allows you the ability to have tables filter each other down. So, if you have your fact and your dimension, your dimension typically always filters down your fact table. But if you enable the bi-directional feature you can turn this relationship so the fact can also filter the dimension.
This is helpful when you’re trying to aggregate across multiple dimensions (many to many type support). Let’s say you want to do a count of customers that bought a product on a given day. You would want your day to be a dimension and your aggregation would be a count of customers from the customer table.
So, it would go from dimension to fact to dimension; you need that bi-directional relationship to allow that filtering to happen. You’re implicitly allowing this action to happen by saying any time any filter comes from the fact table, filter down the dimension.
However, this can cause problems when you’re talking about things like date tables. When you enable bi-directional filtering into a date table and it gets filtered down to that bi-directional relationship, then suddenly your time intelligence breaks. In this case, instead of doing bi-directional relationships and allowing implicit filtering no matter what, try to create any kind of aggregation that you’re trying to do like count a customer as a measure instead of doing it across the entire table.
In this measure you can use the calculate function to create your own type of filtering that is allowed. Essentially what you can do with this measure is you calculate count a customer, but you put a filter in that calculate function to allow filtering to come from the Internet sales table strictly for count of customer or count of date. At that point it won’t cause a bi-directional relationship to happen.
The moral here is avoid implicit types of actions that happen as much as possible. This way you’ll be explicitly defining things and you’ll know exactly what and when something is going to happen, so nothing ends up surprising you in your data model such as your time intelligence breaking.
We all know life can get hectic. Here at 3Cloud, we’re no different. But one of our goals is to learn something new about Azure every day, as things are constantly changing and being updated. Many people are still learning all the amazing things they can do within the Azure cloud and we want to help. Our posts in our Azure Every Day series are a great way to learn more about Azure each week.
For more tips and tricks on Power BI or anything Azure, be sure to follow me on Twitter: @backtoSQL.
Need further help? Our expert team and solution offerings can help your business with any Azure product or service, including Managed Services offerings. Contact us at 888-8AZURE or [email protected].
