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

Re: How to aggregate values in 2 dimensions

It's Working !!! Thank you very much darlove !!!
4 REPLIES 4
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

Highlighted
marciocampos Frequent Visitor
Frequent Visitor

Re: How to aggregate values in 2 dimensions

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

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

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