cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marciocampos Frequent Visitor
Frequent Visitor

How to aggregate values in 2 dimensions

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

 

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

RegionWaveTest NameStartFinish
RJ1A17/07/201918/07/2019
RJ1B18/07/201919/07/2019
RJ2A25/07/201926/07/2019
SP1A15/07/201916/07/2019
SP1B17/07/201919/07/2019

 

Result Expected:

RegionTotal Time
(days)
Working Time
(days)
RJ93
SP43

 

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
marciocampos Frequent Visitor
Frequent Visitor

Re: How to aggregate values in 2 dimensions

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

View solution in original post

4 REPLIES 4
Highlighted
AUaero Regular Visitor
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.

marciocampos Frequent Visitor
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:

 

RegionWaveTest NameStartFinish
RJ1A17/07/201918/07/2019
RJ1B17/07/201918/07/2019
RJ2A25/07/201926/07/2019

 

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

Super User
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

marciocampos Frequent Visitor
Frequent Visitor

Re: How to aggregate values in 2 dimensions

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 100 members 1,613 guests
Please welcome our newest community members: