skip to Main Content

Guest Post by Andrew Todd

Intro by Avi: We’ve been excited about all the new functionality that Power BI Desktop brings 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…

image
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.

Power Pivot, PowerPivot, Power BI Desktop

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.

image
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!

image

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 : )

Andrew Todd

I am a BI professional from Seattle, WA. I enjoy working with all of the Microsoft BI stack, especially Power BI.

This Post Has 6 Comments
  1. Avi hi, many thanks for sharing this powerful feature with us ‘early adopters’. The download file contains the ‘Sales Activity Dashboard’, please advise where I can locate the Variables in DAX Measures and MEDIANX.

    Regards

    Andy

  2. Integration with SharePoint 2013 would be great. The we could Dashboard our data models on a space private to our company and secure.

Leave a Reply

Your email address will not be published. Required fields are marked *