cancel
Showing results for
Search instead for
Did you mean:
Frequent 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

Accepted Solutions
Frequent Visitor

## Re: How to aggregate values in 2 dimensions

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

## Re: How to aggregate values in 2 dimensions

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.

Frequent Visitor

## Re: How to aggregate values in 2 dimensions

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

Highlighted
Super User

## Re: How to aggregate values in 2 dimensions

`[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

Frequent Visitor

## Re: How to aggregate values in 2 dimensions

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

## Helpful resources

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 403 members 3,528 guests
Recent signins:
Please welcome our newest community members: