Guest Post by Andrew Todd
Intro by Avi: We’ve been excited about all the new functionality that Power BI Desktopbrings to the table. Bi-Directional Relationships is one of those new features.
Before this (and still for the users of Excel+PowerPivot), you could force a relationship filter to flow “uphill” and implement many-to-many relationships. However that was done inside your DAX measures (click to read a detailed how-to). Now, with Power BI Desktop, that can be done automatically via bi-directional relationships. Andrew shows us how, using some Dynamics CRM data…
Dynamics CRM: Want to find out which sales calls resulted in a sale? You can with Bi-Directional Relationships in Power BI Designer
Note: Download the example .pbix at the bottom of this this post, which includes the above dashboard, example bi-directional relationships setup and showcasing other new features in Power BI Desktop
Lookup tables in PowerPivot are like reservoirs holding torrents of instructions poised to break free at the click of a slicer tile. When a user clicks on a slicer connected to a lookup table, they open a flood gate and instructions are unleashed to flow downhill to data tables.
In Power BI Desktop, filters can defy gravity! Not only can filters flow downhill from the reservoir into the data tables… they can also flow uphill from data tables to lookup tables! Those instructions flowing uphill into the lookup tables can then spill over to data tables on the other side of the lookup table!
Driving Sales Activity Metrics from the Back Seat
With filters flowing uphill, the filters from the data table side of the relationship can be sent back to the lookup table. In a sense – those filters can actually flow right through the lookup table and down to data tables on the other side! The lookup table itself is filtered and the context ‘splashes’ over to the other data tables.
In the model pictured above, the SalesCalls table has details from each phone call that SalesReps made to prospective clients, which means there are duplicates in the Sales Rep column. After we set filters to flow both ways between SalesCalls and SalesPersonLookup (see screenshot below), even though there are duplicates in the SalesCalls[Sales Rep] column, we can push the Sales Rep filters up to the SalesPersonLookup.
Choose your filter direction: Single (“downhill”, the default behavior) or Both (“downhill” and “uphill”, i.e. bi-directional!)
But why stop there? Since we pushed our filter context to the SalesPersonLookup table, that context will actually make its way to the SalesHistory table on the other side!
Using this technique, we can see which Sales Call produced a sale in the SalesHistory table and even display the total Sales Amount taken from that table, as in the Table below!
Flexibility in Designing Models
The ability to filter in both directions allows for an incredible amount of flexibility in designing a data model. While this cross filtering behavior can be done in Power Pivot, the ability to just flick a switch in the data model without getting too fancy with DAX helps keep measures more clean and understandable.
Download the the .pbix file for some examples of new Power BI features, including:
- Variables in DAX Measures
- New DAX Measures (MEDIANX())
- An Example Dashboard Using Power BI Desktop Visualizations
Avi: Leave us a comment and tell us what are your favorite Power BI Desktop features? What are the features you would like to see? Remember you can submit new ideas and cast your vote for Power BI ideas at their support site. We have our favorites : )
Get in touch with a P3 team member