ambiguity between tables

[kintela]

Hi

I don´t understand why I can not create this relationship between Proyectos and Horas

Each time I try it I get tis message:

In the Horas Table there is no record with proyectoID that does not exist in the Proyectos table and in the Horas Table there is no record with userID that does not exist in Empleados

¿¿¿¿¿

Regards

[Anonymous]

As other mentioned, there is an indirect existing relation.

I ususally need to have direct relation ship so when user click on a table or visualisation, other visualisations auto filter.

To address this, I add a measure which use inactive relationship. 

Dax formula:

USERELATIONSHIP Function (DAX)

https://msdn.microsoft.com/en-us/library/hh230952.aspx

I hope it helps

[Sean]

The ambiguity comes from the fact that you already have a path from Proyectos to Horas

[kintela]

But the relationship betewwen Empleados and Proyectos says that one Employee can be group leader of many Projects but one employee that is not group leader of any proyect can work hours in any project.

So If I want to know the name of the emoployee that has Hours in the Horas table through his userId I only could get the hours of group leaders throug the filed "jefeGrupo/group leader". No?

[v-sihou-msft]

@kintela

It looks like a Many to Many scenario. One Employee can work for multiple projects, and one project is mady my multiple employees. So you should have a fact table between Employee and Project table to decribe the mapping for getting the working hours associated with each employee.

You can refer to the Many to Many solution white paper on sqlbi:

https://www.sqlbi.com/topics/many-to-many/

Regards,

[kintela]

That's the idea...

In HorasFULCRUM table I hav the relation between Empleados and Proyectos, that is, In HorasFulcrum I have the Empleados.userids and the Proyectos.IDs that represebts the hours worked by the employees in the diferente projects.

But I can not activate the relationship and I don't know why...

regards

[dedelman_clng]

The relationship is there, it just isn't active. 

When creating measures you can use the USERELATIONSHIP() function to temporarily activate the relationship between project and hours for the duration of the calculation.

Hope this helps

David