Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VivianC
Helper I
Helper I

Sum on Conditions Linked ALSO not Linked to the Dimension Related

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.

 

Image 3.png

Here is the dummy data for copying/testing:

Data table 1:     
NameHoursLogged DateProjectConditionResult should be:
Worker A0.512/25/2019BLANKCondition X<= Link to NO Project, logged date falls into Project A range, counted to Project A, Condition X
Worker A11/2/2020BLANKCondition Y<= Link to NO Project, logged date falls into both Project A & B range, counted to Condition Y for both Project A & B
Worker A23/20/2020BLANKCondition Y<= Link to NO Project, logged date falls into Project B range, counted to Project B, Condition Y
Worker B13/15/2020BLANKCondition Y<= Link to NO Project, logged date falls into Project B range, counted to Project B, Condition Y
Worker B53/9/2020BLANKCondition Y<= Link to NO Project, logged date falls into both Project A & B range, counted to Condition Y for both Project A & B
Worker B11/1/2020Project ACondition X<= Link to a project, do not count at all
Worker C23/2/2020Project ACondition Y<= Link to a project, do not count at all
Worker D34/2/2020Project BCondition X<= Link to a project, do not count at all

 

Data table 2:  
 Project Start DateProject Complete Date
Project A12/20/20193/10/2020
Project B1/1/20204/1/2020

 

Thank you!!

1 ACCEPTED SOLUTION
VivianC
Helper I
Helper I

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] ))

 

 

View solution in original post

9 REPLIES 9
VivianC
Helper I
Helper I

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] ))

 

 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@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 

https://techcommunity.microsoft.com/t5/excel/sum-array-based-on-date-range-date-range-changes-per-di...

 

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:9.png
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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft 

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:

Image 3.png

 

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.

11.png

>>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. 

12.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

amitchandak
Super User
Super User

@VivianC , how to deal with overlapping durations

@amitchandak 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.