Power BI relationship not working

[maheshwarineha]

An active relationship is there but still not getting the correct no's in report.

projects are connected to projectcontractors - one to many

projectcontractors are connected to contractors - many to one

[maheshwarineha]

projectID is from project table ID column

contractors = DISTINCTCONT([CONTRACTORS_ID])

[danextian]

But where is contactors id from?  It appears to me that you're aggregating the column from the dimension table. The filter from project will not flow to that table based on the screenshot of the relationship you posted ( which I can no longer find) thus you're seeing the same value for all rows.

[maheshwarineha]

Okay I got your point.

But how to calculate the contractors for each projectId?

[danextian]

Does the fact table have contractor id? What columns did you use to connector contractor and fact?

[maheshwarineha]

Table 1 - projects - contains id

Table 2 - projectcontractors - contains left id and right id

left id is connected with projects id and right id is connected with contractors id

Table 3 - contractors - contains id

projects are connected to projectcontractors - one to many

projectcontractors are connected to contractors - many to one

[danextian]

Is  one contractor id equivalent  to one right id in table2? If so you can use a distinct count on table 2 right id. Otherwise, you can create a calculated column in  table2 (RELATED(contractors[contractorid]) and do a distinctcount on that calculated column.

[danextian]

Hi @maheshwarineha 

Where are the colums in your visual from?