Forum Discussion

maheshwarineha's avatar
maheshwarineha
Frequent Visitor
2 years ago

Power BI relationship not working

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 t...
Need Help
danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

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's avatar
maheshwarineha
Frequent Visitor
2 years ago

Okay I got your point.

 

But how to calculate the contractors for each projectId?

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

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

    • maheshwarineha's avatar
      maheshwarineha
      Frequent Visitor
      2 years ago

      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's avatar
        danextian
        Icon for Super User rankSuper User
        2 years ago

        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.