Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, if someone can help me sum as below using Dax or whatever (I find it hard to do, so I tried my best to describe the data and final table).
I want to sum hours linked to NO project (=BLANK project), but WITHIN each Project's start and end range. And by person, by project.
Here is the dummy data for copying/testing:
Data table 1: | |||||
Name | Hours | Logged Date | Project | Condition | Result should be: |
Worker A | 0.5 | 12/25/2019 | BLANK | Condition X | <= Link to NO Project, logged date falls into Project A range, counted to Project A, Condition X |
Worker A | 1 | 1/2/2020 | BLANK | Condition Y | <= Link to NO Project, logged date falls into both Project A & B range, counted to Condition Y for both Project A & B |
Worker A | 2 | 3/20/2020 | BLANK | Condition Y | <= Link to NO Project, logged date falls into Project B range, counted to Project B, Condition Y |
Worker B | 1 | 3/15/2020 | BLANK | Condition Y | <= Link to NO Project, logged date falls into Project B range, counted to Project B, Condition Y |
Worker B | 5 | 3/9/2020 | BLANK | Condition Y | <= Link to NO Project, logged date falls into both Project A & B range, counted to Condition Y for both Project A & B |
Worker B | 1 | 1/1/2020 | Project A | Condition X | <= Link to a project, do not count at all |
Worker C | 2 | 3/2/2020 | Project A | Condition Y | <= Link to a project, do not count at all |
Worker D | 3 | 4/2/2020 | Project B | Condition X | <= Link to a project, do not count at all |
Data table 2: | ||
Project Start Date | Project Complete Date | |
Project A | 12/20/2019 | 3/10/2020 |
Project B | 1/1/2020 | 4/1/2020 |
Thank you!!
Solved! Go to Solution.
I found a way to do it 🙂
1. First create a table using below:
Worker Project Table =
VAR f =
FILTER ( Table1, 'Table1'[Project] <> BLANK () && Table1[Worker] <> BLANK () )
RETURN
CALCULATETABLE (
SUMMARIZE (
Table1',
Table1'[Worker],
Table1'[Project]
),
KEEPFILTERS ( f )
)
It gives me a table with unique worker name with unique project.
2. Create a relationship on "Worker" between Table1 and "Worker Project Table".
3. Then use "Lookupvalue" look up each row's project's start and complete date. So in this new table, "Project" is just a label, calculation is based on each row's worker name, start date and complete date
4. Creates a new column (not measure, needs to be a column, so it can compare the dates per row) in this "Worker Project Table" calculates from Table1 using below Dax:
Hours LinkToNoProjectButInProjectRange = CALCULATE(Sum(Table1[Hours]),
Filter(Table1, Table1[Project]= blank() &&
DATEVALUE(Table1[Logged Date])>= Datevalue('Worker Project Table'[Project Start Date]) && Table1[Logged Date]<= 'Worker Project Table'[Project Complete Date] ))
I found a way to do it 🙂
1. First create a table using below:
Worker Project Table =
VAR f =
FILTER ( Table1, 'Table1'[Project] <> BLANK () && Table1[Worker] <> BLANK () )
RETURN
CALCULATETABLE (
SUMMARIZE (
Table1',
Table1'[Worker],
Table1'[Project]
),
KEEPFILTERS ( f )
)
It gives me a table with unique worker name with unique project.
2. Create a relationship on "Worker" between Table1 and "Worker Project Table".
3. Then use "Lookupvalue" look up each row's project's start and complete date. So in this new table, "Project" is just a label, calculation is based on each row's worker name, start date and complete date
4. Creates a new column (not measure, needs to be a column, so it can compare the dates per row) in this "Worker Project Table" calculates from Table1 using below Dax:
Hours LinkToNoProjectButInProjectRange = CALCULATE(Sum(Table1[Hours]),
Filter(Table1, Table1[Project]= blank() &&
DATEVALUE(Table1[Logged Date])>= Datevalue('Worker Project Table'[Project Start Date]) && Table1[Logged Date]<= 'Worker Project Table'[Project Complete Date] ))
Hi @VivianC,
I'd like to suggest you create a calculated table to expand your table 2 records to store the details records of each day in the date range that defined by two fields.
Expand =
VAR _calendar =
CALENDAR ( MINX ( ALL ( T2 ), [Start] ), MAXX ( ALL ( T2 ), [END] ) )
RETURN
SELECTCOLUMNS (
FILTER ( CROSSJOIN ( T2, _calendar ), T2[Start] <= [Date] && T2[END] >= [Date] ),
"Project", [Project],
"Date", [Date]
)
Then you can use this table date fields to link table 1 date fields and create a matrix with 'Expand' table project, table 1 Name as row, table1 condition field as the column, and 'sum' of the hours as value.
If above not help, please share some dummy data with a similar data structure to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@v-shex-msft Hi, thank you for replying...I don't think it will work, the final table needs to have Project as the top demension, then name, by conditions. That's the difficult part, we need Project as the top level demension, but the hours to be grouped in are NON-Project related while falls into each project's time range. (If in Excel, sumifs cannot work, because there are hundreds of projects, cannot sumifs based on one specific cell (then drag down), somehow needs to look up for THAT project's time range first, then sum hours. I actually posted this in Excel forum as well just to see if someone can think of an array formula to perform it in Excel, no answet yet
I have attached the dummy data in the original request, maybe I didn't understand your solution correctly. Thank you!
Hi @VivianC,
In fact, these operations almost suitable for your requirement:
I also attached the sample pbix file below, if you still not clear about these steps, you can check it.
one more question: how did you handle the blank works who logged the date matches with multiple project ranges? In your expected result, it has not added up to project B. (notice: In my sample, I aggregated these records to multiple ranges if they meet the data range):
Regards,
Xiaoxin Sheng
Hi Sheng,
First, thank you for taking the time to work on this. Unfortunately, it is not what I am trying to achieve. Maybe I didn't describe it clearly: the result table should be NON-project linked ONLY. So Worker C and D should not have any numbers at all. Please take a look at the "Result should be" column:
Thank you!
Hi @VivianC,
So you mean you want to filter the records already mapping to a project in the raw table, right?
If this is the case, you only need to add one more step to modify the sample. You can drag the raw table project field to the visual level filter of matrix visual and choose 'BLANK' to filter not matched records.
>>Not sure why my post doesn't show any responses anymore (I did try to edit the table too many times, so maybe it was not posted successfully).
BTW, I think this should be related to the community update, it moves the notice option to the 'additional option' and not checked on this option by default.
Regards,
Xiaoxin Sheng
Hi Sheng,
Thanks for replying, the final result falls into BLANK project but also needs to fit in: each project's start and end date. One worker's entered hours can falls into multiple project's start and end date when link to none of the projects.
@VivianC , how to deal with overlapping durations
Do you mean if project A overlaps project B like shown in the sample? Ignore it. Please see the final table: For each project, count the hours that each worker DOESN't work for ANY project, then how many hours falls into THAT project's start and complete range. Thank you!
Translate to Spanish:
You mean if Project A overlaps Project B as shown in the sample? Ignore it. Please see the final table: For each project, count the hours that each worker does NOT work for ANY project, then how many hours fall into the start and end range of THAT project. Thank you!
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |