cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## How to aggregate values in 2 dimensions

hi folks!
I need some help here !! Someone could help me? 🙂

I have a table with "test records" and I need to extract from it the "test total duration" and "days worked per test wave".

See the table (fATP):

 Region Wave Test Name Start Finish RJ 1 A 17/07/2019 18/07/2019 RJ 1 B 18/07/2019 19/07/2019 RJ 2 A 25/07/2019 26/07/2019 SP 1 A 15/07/2019 16/07/2019 SP 1 B 17/07/2019 19/07/2019

Result Expected:

 Region Total Time(days) Working Time(days) RJ 9 3 SP 4 3

Total Time (RJ) = 26/07/2019 - 17/07/2019 = 9 days

Total Time (SP) = 19/07/2019 - 15/07/2019 = 4 days

for this metric i used the follow DAX expression: (working)

`Total Time = DATEDIFF(MIN(fATP[Start]);MAX(fATP[Finish]);DAY)`

but I couldn't make a DAX expression to get value by "working time".

Working Time (RJ) = (18/07/2019 - 17/07/2019) + (19/07/2019-18/07/2019) + (26/07/2019-25/07/2019) = 3 days

Working Time (SP) = (16/07/2019 - 15/07/2019) + (19/07/2019-17/07/2019) = 3 days

Any idea how to solve this?

1 ACCEPTED SOLUTION
Regular Visitor
It's Working !!! Thank you very much darlove !!!
4 REPLIES 4
Advocate II

Why not create a calculated column to calculate the days between start and finish for each row?  Working Days would be the sum of the the calculated column.

Regular Visitor

This is not possible because we can have 2 tests performed on the same day and in this situation we have counted only 1 working day and not 2. See the example below:

 Region Wave Test Name Start Finish RJ 1 A 17/07/2019 18/07/2019 RJ 1 B 17/07/2019 18/07/2019 RJ 2 A 25/07/2019 26/07/2019

Working Time (RJ) = (18/07/2019 - 17/07/2019) + (26/07/2019-25/07/2019) = 2 days

Anonymous
Not applicable
`[Working Time] =var __onlyOneRegionVisible = hasonevalue( T[Region] )var __differentTimeBounds =    summarize(        T,        T[Start],        T[Finish]    )var __workingTime =    sumx(        __differentTimeBounds,        T[Finish] - T[Start]    )return    if(        __onlyOneRegionVisible,        __workingTime    )`

Best

Darek

Regular Visitor
It's Working !!! Thank you very much darlove !!!

## Helpful resources

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Click here to read more about the July 2021 Updates

#### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

#### Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors