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

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

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 123 members 1,825 guests
Recent signins: