When you create a new relationship between two table in Power Bi you have two different Advaced Options. It’s really import to understand these Advanced options because They have a huge impact on reporting.
This part is simple, it’s similar to powerpivot or ssas relationship:
- Many to one – This is the default type most common. It means that the column in a table can have multiple instances of a value and the other related table, often known as the lookup table includes only one instance of a value.
- One-to-one– means that the column in a table contains only one instance of a specific value and the other related table includes only one instance of a specific value.
Cross direction Relation
This is more difficult to understand because this option is new feature. in short words:
- Both – This is the most common default direction. It means that for purposes related to the filters both tables are treated as if they were a single table. This procedure works well with a single table that includes a number of lookup tables that enclose it. An example is a table of actual values of sales with a lookup table for division. Often it is a star schema configuration (a central table with different lookup tables)
- Single – means that the filter options in the related tables are applied to the table in which the values are aggregated. If you import a Power Pivot Table in Excel 2013 or in an earlier data model, all the reports will have one direction.
The second one is a typical relation between two table in Powerpivot or SSAS. But the First one is very particular. Try to explain it with an example, we start with a classica relation:
If i create a power bi report that count the Number of project by Employee the result is this:
The relation work like a normal ssas or pivot table relation and it’s impposible using a oly a simple count to slip correctly the value. But if i changethe advance option in Company Employee and Company project and select Both the result is this:
Now it’s working correclty. Beware of this type of option, because as you can see has a great influence on the type of report and the result obtained