Managing Relationships: Unable to get the data to reflect properly.

[SirTim]

Hi,

I am new to this so please excuse my ignorance if I missed something. I am trying to build a data model that will allow me to view the data in various ways and I am only able to get it to work one way or the other.

I get my data via an API from Jira to populate the tables in Power BI. I have a number of Dim tables (I have prefixed these with lst_) and a number of fact tables (those not prefixed with lst_). There iscommon Dim table data across the fact tables.

The way the linkages work in Jira, from my understanding, is via the outward and inward Issue Keys. The way my data works is a Contracting Candidate is linked to a Position and a Position is linked to a Client. I am able to get this view in Power BI working but as soon as I want to for example view Clients then Positions then Candidates, however, the visualisation just reflects as an error and informs me that relationships are not able to be determined. Even when I try to add a Dim item I get the same error, however, when I try and add a relationship it is added but not activated as it already has a link to another fact table. I have tried changing the relationships adding briding tables etc. But, again, to avail.

Below is an example of some of the tables that I have in my model.

Some advice in terms of how can I build the relationships to enable me to slice and dice the information in any form i.e. Client then Positions then Candidates and make use of the Dim tables would be greatly appreciated.

I have trawled through the community and read the documentation but still can't work it out. 

Regards,

Tim.

[v-jingzhang]

@SirTim 

What are the columns used to create relationships on? What are the columns selected for the visualizations? The error "Can't determine relationship between fields" may be caused by the cross filter direction and cardinality, and the column used from a table in the visualization cannot filter another column from another table.  

See if these links can help you troubleshoot it: 

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-troubleshoot

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Active & inactive relationships: 

https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

[SirTim]

Thanks for the reply. I have had a look and am still unable to get the view I want.

In the first model, I get most of the data I want. However, when I add a table for positions I get none of the 16 positions I am expecting. The result is in the last image.

If I change the model and try using a bridging table I don't get what I want. Result in the second image. The Submitted Candidates table is linked to the Positions table and the Positions table is linked to the Client Table. I want to be able to build views on either of these.

Any other suggestions would be appreciated.

[lbendlin]

General advice is to link from a "dimension" table to a "fact"  table in a 1:M relationship with unidirectional search filter.  That way you end up with a desirable "star"  or "snowflake"  schema, at least in spirit.  Try to stay away from M:M relationships , and for 1:1 relationships try to enforce a single filter direction.