Relation issue

[nbaudry]

Dear Forum,
Since a couple of days, I am dealing with an issue of relation table.
I have 5 tables:
- Calendar
- Project information
- Load of the projects
- Capacity of the project manager
- Project manager table
I need to analyze the load/capacity of the project manager sort by priority.
My issue in the summary tab I see also the project of Pierre but he hasn’t any project in priority 2.
Many thanks for your help
N.

File 

[v-juanli-msft]

Hi @nbaudry 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @nbaudry 

As tested, i make some transformations in your model.

( You could downlaod my pbix file, in Edit queries, open Advanced editor, you can see the M code and paste it in your file,

Or you can click on each step and find details for steps )

Steps below:

   In Edit queries:

1. in "capacity" table,

  1.1  merge queries from "project" table based on column "Name" (Capacity table), column "Project manager" ("project" table),

         when expanding, only select "id" to show on the "capacity" table.

  1.2 select "Name", "date", "Project.ID", merge columns, then we get a new column "name-date-id-capacity".

2. in "load" table, select "Project Manager", "date", "Project ID", merge columns, then we get a new column "name-date-id-load".

3. merge new queries from "capacity" table and  "load" table, based on "name-date-id-capacity" and "name-date-id-load",

    select "full outer" for "Join kind",  when expanding, only select "Load (days)" to show on this new table.

4. right click on the "capacity" table and  "load" table, uncheck "enable load", 

    in this case, the two tables won't show on the "Data Model" view, so to save the data size and improve performance for reports.

    Cloase&&apply.

In Relationship view.

Finally,

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nbaudry]

Hello Maggie,

Thanks a lot for your job and your time.

For the example I add only the project manager in the load tab but for my application I have the team members who are not in the project tab ... I have to make some tests but instead of to do this :

merge queries from "project" table based on column "Name" (Capacity table), column "Project manager" ("project" table),

         when expanding, only select "id" to show on the "capacity" table.

I will try to merge the capacity tab with the load tab.

Thanks again

Nicolas

[v-juanli-msft]

Hi @nbaudry 

Feel free to let me know if you have any problem.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.