Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
steigelbill
Frequent Visitor

Multi Level Count and Filter within single day

Screen Shot 2017-06-20 at 4.14.07 PM.png

So, I am trying to use PowerBI analysis to figure out employee throughput. The data set has date & Time, User, Order # and zone.  So I need to count the number of orders done in a zone and divide it by the duration they were in that zone.

 

I can do that!  My problem is calculating when a person changes between zones.

 

Currently to determine duration when someone is in a zone all day, I subtract the start time from end time (and multiply by 24): 

INT Start Time = CALCULATE(MIN(ActivityTracking[Time]),Filter(All(ActivityTracking),ActivityTracking[Zone]="INT"),Filter(All(ActivityTracking),ActivityTracking[User]=Users[User]))

INT End Time = CALCULATE(MAX(ActivityTracking[Time]),Filter(All(ActivityTracking),ActivityTracking[Zone]="INT"),Filter(All(ActivityTracking),ActivityTracking[User]=Users[User]))

 

What I would need to do is sum up the duration of multiple instances of being in a zone, and have that total be the divisor.

 

Any ideas would be appreciated.

2 REPLIES 2
v-jiascu-msft
Employee
Employee

Hi @steigelbill

 

We can add a row to evaluate the duration per user per zone. Then we can sum up the durations of many instances.

 

DuringPerUserPerZone =
 (
    CALCULATE (
        MAX ( ActivityTracking[Date Created] ),
        FILTER (
            ActivityTracking,
            ActivityTracking[User] = EARLIER ( ActivityTracking[User] )
                && ActivityTracking[Zone] = EARLIER ( ActivityTracking[Zone] )
                && ActivityTracking[Date Created].[Date]
                    = EARLIER ( ActivityTracking[Date Created].[Date] )
        )
    )
        - CALCULATE (
            MIN ( ActivityTracking[Date Created] ),
            FILTER (
                ActivityTracking,
                ActivityTracking[User] = EARLIER ( ActivityTracking[User] )
                    && ActivityTracking[Zone] = EARLIER ( ActivityTracking[Zone] )
                    && ActivityTracking[Date Created].[Date]
                        = EARLIER ( ActivityTracking[Date Created].[Date] )
            )
        )
)
    * 24

 

DuringPerUserPerDay =
CALCULATE (
    SUM ( ActivityTracking[DuringPerUserPerZone] ),
    FILTER (
        ActivityTracking,
        ActivityTracking[User] = EARLIER ( ActivityTracking[User] )
            && ActivityTracking[Date Created].[Date]
                = EARLIER ( ActivityTracking[Date Created].[Date] )
    )
)

 

NumberOfOrders =
CALCULATE (
    COUNT ( ActivityTracking[LPN] ),
    FILTER (
        ActivityTracking,
        ActivityTracking[User] = EARLIER ( ActivityTracking[User] )
            && ActivityTracking[Date Created].[Date]
                = EARLIER ( ActivityTracking[Date Created].[Date] )
    )
)
Result =
[DuringPerUserPerDay] / [NumberOfOrders]

Or we can use a measure with the new column.

MesureResult =
SUM ( ActivityTracking[DuringPerUserPerZone] ) / COUNT ( ActivityTracking[LPN] )

Multi Level Count and Filter within single day .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @steigelbill,

 

Could you please mark the proper post as answer if it worked? More about this topic, please feel free to post here.

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.