Search code examples
filterpowerbirelationshipdaxpowerpivot

Filtering a Fact Table by several Dimension Tables in a matrix (Power BI / Power Pivot)

I have a very basic question regarding filter context in Power BI (DAX).

The data model is composed by 3 tables:

  • Dimension1 "Residence Tenants" (whose fields are: 1.- ID Tenant [Primary Key]; 2.- Tenant Name; 3.- Academic Year)

  • Dimension2 "Residence Location" (whose fields are: 1.- ID Residence [Primary Key]; 2.- Residence Name; 3.- Beds for Residence)

  • Fact1 "Data Leasing Contracts" (whose fields are: 1.- ID Tenant [Foreign Key]; 2.- ID Residence [Foreign Key]; 3.- Lease Rents in €)

After setting the relationships (one to many) among the 3 tables (Foreign Keys in Fact Table to the Primary Keys in Dimension Tables), I build a matrix composed of the following fields mixing filters from the 2 dimension tables:

As Rows (filter context):

  • Residence Name (Dim2)

  • Tenant Name (Dim1)

As data measure: Sum of Lease Rents

My questions:

  1. Are the filters going to work correctly as I am using fields from the 2 different Dim tables (which are not related between each other)?. The relationship of both of them with Fact table are correct (both are "One to Many" and Downstream), but I am not sure that the filter flow in the matrix is correct when mixing such 2 dimensions fields.

  2. If it does not work properly, maybe the solution is to use CrossFilter function so that the filter flow goes upstream from the Fact Table to the other Dimension Table? In that case the 3 tables would correctly connected?

Thanks in advance

Solution

  • The answer to your first question is yes, filters are going to work correctly. The filter context is composed of filter from dim1 and dim2. And it propagates automatically in your situation.

    Filter context is automatically propagated from the one side of the relationship to the many side, whereas it is not propagated from the many side to the one side.

    As for your second question, the use of cross filter or bi-directional filter is used when you measure is defined in the one side of the relationship (In a dimension table) in this case the filter does not propagate automatically and there for you need to use cross filter or bi-directional filter.

    Visit : https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/