topic Re: Calculate SUM of allocation over a related table with dynamic year filter in DAX Commands and Tips

Calculate SUM of allocation over a related table with dynamic year filter

ileana2019 — Wed, 08 Dec 2021 19:05:11 GMT

Hello,

I am having troubles calculating the sum of Allocation from a related table, based on the year filter selection.

I have the following SharePoint lists (I used Excel to simplify):

tblAllocation
Start_Date	End_Date	Year_A	Allocation_in_Days	Employee	Project
01-01-21	31-12-21	2021	40	Anna	Project 1
01-01-21	31-12-21	2021	135	Linda	Project 1
01-01-22	31-12-22	2022	60	Anna	Project 1
01-01-22	31-12-22	2022	95	Linda	Project 1
01-01-22	31-12-22	2022	15	Hans	Project 1

tblProjects
Title	Start Date	End Date
Project 1	19-10-20	30-12-22

tblTasks
Title	Project	Start Date	Due Date	Cost In Days
Task 1	Project 1	10-05-21	06-08-21	95
Task 2	Project 1	10-01-22	25-02-22	30
Task 3	Project 1	16-05-22	10-06-22	15
Task 4	Project 1	06-06-22	17-06-22	0
Task 5	Project 1	30-05-22	03-06-22	0
Task 6	Project 1	30-08-21	10-12-21	35
Task 7	Project 1	25-04-22	06-05-22	14
Task 8	Project 1	09-05-22	13-05-22	5
Task 9	Project 1	20-06-22	24-06-22	5
Task 10	Project 1	28-02-22	18-04-22	31

What I would like is to display in a Power BI table the following:

Title (tblProjects)	Start Date (tblProject)	End Date (tblProjects)	Cost In Days (tblTasks)	Allocation_in_Days (tblAllocation)
Project 1	19-10-20	30-12-22	230	345

The relationships are:

-tblProjects-tblTasks: 1 to many

-tblProjects-tblAllocation: 1 to many

And have a filter for the tblTasks Start Date year:

Whenever I select a year, the Allocation_in_Days should be calculated accordingly (example: if I select year 2021, the Allocation_in_days should be 175). That is now not the case; How can I do that?

Thank you!

Re: Calculate SUM of allocation over a related table with dynamic year filter

smpa01 — Wed, 08 Dec 2021 18:02:30 GMT

ileana2019 how all these tables are connected?

Re: Calculate SUM of allocation over a related table with dynamic year filter

ileana2019 — Wed, 08 Dec 2021 19:04:40 GMT

The relationships are:

-tblProjects-tblTasks: 1 to many

-tblProjects-tblAllocation: 1 to many

Re: Calculate SUM of allocation over a related table with dynamic year filter

smpa01 — Wed, 08 Dec 2021 19:07:25 GMT

ileana2019 a measure with SUM should give you what you need

Re: Calculate SUM of allocation over a related table with dynamic year filter

ileana2019 — Wed, 08 Dec 2021 19:12:38 GMT

Hi smpa01

Indeed the sum works to have the total for all years (inside tblAllocation); I also created a measure:

CALCULATE(SUMX(RELATEDTABLE(tblAllocation), tblAllocation[Allocation_in_Days])) that gives the same result; However, when I filter(=slicer) on the Year (of the tbTasks StartDate) then the sum is still the total sum. It should be the sum only for that specific year (inside tblAllocation).

Re: Calculate SUM of allocation over a related table with dynamic year filter

David-Ganor — Wed, 08 Dec 2021 19:36:26 GMT

Hi Ileana.

A dimension should be created for project and year (concatenated).

Please review the attached PBI 🙂

https://drive.google.com/file/d/1nQA-eDHIet1AbcIJW8IlxiZ3lOorSB4A/view?usp=sharing

(download)

Re: Calculate SUM of allocation over a related table with dynamic year filter

ileana2019 — Thu, 09 Dec 2021 11:23:18 GMT

Hi David-Ganor ,

Thank you for the file! How do I create the Dim Project_Year table? Is that a left outer join between tblAllocation and tblProjects? From where is the Year column inside table Dim Project_Year coming from? From tblAllocation or tblTasks? It should come from tblTasks (since the filter needs to filter for the tasks that start in a certain year). Is there another way than creating the intermediate table (like with using CALCULATE, FILTER, RELATEDTABLE etc)?

Thank you for your help!

Re: Calculate SUM of allocation over a related table with dynamic year filter

David-Ganor — Thu, 09 Dec 2021 17:16:46 GMT

Hi ileana2019

The logical reason for creating it as a Dimension - is somewhat a "business" logic.

Every project has tasks and employees that can contribute to this project

Every task (within project)- should be done in a certain year - and has its cost

Every employee can contribute a certain number of days for a certain project in a certain year.

Project --> Task --> Year & Cost

Project -->Employee --> Year & Contribution.

Therefore the Project_Year_Key is a Shared dimension for the "Cost" (tblTasks) and the "Contribution" (tblAllocation).

It should filter both. And performance wise - it should be more efficient than trying to "manipulate" it inside a measure.

Regarding the creation of this dimension, I've got a few options/suggestions:

creating "Year" table (kind of dimDate table) and Cartesian join it with tblProject - this way you generate all possible options
Distinct - of Year & Project from tblTask (can be done as a calculated table OR in the Power Query)
Use option no. 2 BUT Union with Year & Project from the tblAllocation (remove duplicates after Union) -- for a case where there can be an employee which has the capability to contribute but there are no relevant tasks for him to participate in.

Hope it helps.

If you need further assistance with creating that Dimension - please reply.

Re: Calculate SUM of allocation over a related table with dynamic year filter

ileana2019 — Thu, 09 Dec 2021 21:15:07 GMT

Hi David-Ganor ,

After your first reply, I spent some time today to read more about the star schema modeling, fact tables and dimensions tables. I also created the dimension table using a query: selectcolumns where I selected the year and project from tblAllocation (but now that I read your post, it should have been from tblTasks), then I added the key projectyear(by concatenating the 2 columns), then i removed the duplicates. Anyhow, your solution totally worked! Thank you so much! It made me also rethink my entire data modeling, where I was using a lot of different relationships between the tables and a lot of calculations (like lookups, related etc.). I will now try to implement the star schema, I guess a new question post will follow soon. I ll mark your response as solution. Thank you again for taking the time to help with this!