One-to-Many Relationship in to "to-be" filtered table

[Anonymous]

I am trying to set up my relationships to minimize work further down the road. The three tables in question are PhaseData, TaskData, and HourData.

Phase Data has unique phases. Task Data has unique tasks per phase item. However Task codes are not unique across all phases. This is to say Phase 1 can have a task with code 2000, but there can also be Phase 10 with Task code 2000. This is causing problems with creating a 1 to many relationship between the TaskData and HourData which has a phase code and task code column.

At the moment I can't have an active PhaseData to HourData connection as well as a TaskData to HourData with the 1-to-many relationship.

Any help is appreciated!

[ibarrau]

Hi, I think you can show us more to help with this, but let me try anyway. You need an unique key in hours that should be in tasks too. For example, the hour. If you don't have it, try creating a combination for this like a new text column "Phase-Task". The scenario looks like the Task is going to be a fact table and you need to filter by your dimensions phase and hour.

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is the relationship I would like but I can't have active 1 to many

---

@ibarrau wrote:

Hi, I think you can show us more to help with this, but let me try anyway. You need an unique key in hours that should be in tasks too. For example, the hour. If you don't have it, try creating a combination for this like a new text column "Phase-Task". The scenario looks like the Task is going to be a fact table and you need to filter by your dimensions phase and hour.

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

This is how I would like the relationships to look. At the moment I can't have both active 1-to-many relationships between phase data and task data; hope this makes it more clear.

There is also a shot of how the data structure looks.

[ibarrau]

Ok. I was checking your data and I think the best way to solve this is merging task and hour tables together. They both have taskcode and phasecode. Create a new column combining this two values. Then merge them together by that code. After that, you should have only one table (like a fact table) and relate it with dimensions (employee, phase, even create an autogenerated HourTable or TimeTable). That way you will create a star schema. You have to pre process and transform the data before apply.

You can also just create this new combination of columns (taskcode and phase code) for each table but you will still need to make many to many. I don't recommend this way.

Regards,

[YJ · Accepted solution]

Hi,
You have a 2 way path from phasedata to hourdata ( one direct , one via taskdata ) this provides confusion. Moreover your task code is not uniquely defined by a single field ( each taskname is defined by a combination of phasecode and taskcode , asaik its difficult to do this in powerbi )

The easy work around is to concatenate newtaskcode = phasecode & taskcode
( this can be done either at source or powerquerry )
Now you have
Taskdata table :
[Phasecode] [newtaskcode] [taskname]
And your hourdata table :
[newtaskcode][hours][date]

Then u have a 1 to many from phasedata to taskdata and another 1 to many from taskdata to hourdata .

Hope it helps
Sk

[Anonymous]

Good thinking! This seems the way to go. Thank you!

[Anonymous]

@Anonymous

I would personally append the TaskData and HourData.

This way the dimension tables can be used like a traditional star scheme.

Kind regards
Joren Venema

Data & Analytics Consulant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

[lc_finance]

Hi @Anonymous ,

what about creating a Many to Many relationship?

Regards

LC

[Anonymous]

---

@lc_finance wrote:

Hi @Anonymous ,

what about creating a Many to Many relationship?

Regards

LC

---

Even with the many to many, it doesn't let me set the relationship as active.