Guest Post by Andrew Todd
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…
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.