Can't create relationship between Fact and Dim

[RemiAnthonise]

Hi guys,

I've got the following problem:

In my model I've got Fact and Dim tables. I have a desired situation, see below.

 

 
In my model, you can find these fields in the following tables:

A = FactClient
B = District2017 in FactWijkCodebuurt
C = DimCity
D = DimCity
E = Township2017 in FactWijkCodebuurt
F = Neighborhood2017 in FactWijkCodebuurt

I've got a problem with the relationship between FactClient and FactWijkCodebuurt. In fact, FactWijkCodebuurt must be a Dim table so I've tried to create a 'bridge table' so I have a DimWijkCodebuurt. See image below:

All my data is testdata so it isn't confidential. You can find my .pbix below. I hope the problem is clear, if not please let me know.

I've did some tests with Lookupvalues but I didn't get it right. Maybe this is not the right way ...

https://www.dropbox.com/s/dp5ggm2sluxmqd6/CBS%20test.pbix?dl=0 

[v-yuta-msft]

Hi RemiAnthonise,

Because in your digram, table 'FactClient' and 'FactWijkCodeBuurt' doesn't have direct relationship with each other, they relationship between them is many to many. So once one row has been filtered in table 'FactClient', power bi doesn't know which row should be selected in table 'FactWijkCodeBuurt'. However, you can use aggregation(average or sum) value in your chart as below:

 

Regards,

Jimmy Tao

[Anonymous]

Hi @RemiAnthonise,

The data values of Accountnumber in FactClient do not have an entry in the DimWijkCodeBuurt table and also in FactWijkCodeBuurt.

Please check or add the values of Accountnumbers - 654,664 and 1676 in the above two tables and then check it out.

Cheers

CheenuSing