Power BI cross direction Relation

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.

Immagine 044

Cardinality

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:

Immagine 045.png

If i create a power bi report that count the Number of project by Employee the result is this:

Immagine 046

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:

Immagine 047.pngNow 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

 

 

 

Annunci

Un pensiero su “Power BI cross direction Relation

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...