Forum Discussion

jacosmit's avatar
jacosmit
Regular Visitor
7 years ago

Access control reports

Would dearly appreciate a set of fresh eyes.

I've been tasked with building a report that provides some insights as based on data from an access control db (sample data attached) The idea is to provide monthly reports that would be provided to the divisions that would highlights key stats.

These include:

 

1- List of staff per division that spent most time on site during the month (top 10)

2- List of staff per division that spent the least amount of time on site during the month (bottom 10)

 

My thought process was to create measures that would

1) Write earliest time per day per employee into new column

2) Write latest time per day per employee into another new column

3) Minus latest time with earliest time in a third new column and rank to get top and bottom lists.

 

Problem is, when I work with "CALCULATE" I am getting unexpected results, especially when trying to apply multiple filters to the measure for example:

Earliest Time = CALCULATE(MINX(Query1,Query1[TIME]),ALLEXCEPT(Query1,Query1[USER_ID],Query1[DATE].[Day]))
 
There has to be a better way to go about this!?
 
DATETIMETERMINALDIVISIONUSER_ID
Friday, 02 November 201806:32:34Staff Boom INDivision 01Joe Soap
Friday, 02 November 201806:33:07Boom Basement EntDivision 01Joe Soap
Friday, 02 November 201806:34:20Basement LobbyDivision 01Joe Soap
Friday, 02 November 201806:35:333rd Floor NorthDivision 01Joe Soap
Friday, 02 November 201807:03:453rd Floor NorthDivision 01Joe Soap
Friday, 02 November 201808:18:203rd Floor NorthDivision 01Joe Soap
Friday, 02 November 201808:43:59Visitor Boom INDivision 02Jan Niemand
Friday, 02 November 201808:44:30Staff Parking INDivision 02Jan Niemand
Friday, 02 November 201808:49:11Turnstile North 1 INDivision 02Jan Niemand
Friday, 02 November 201809:49:483rd Floor NorthDivision 01Joe Soap
Friday, 02 November 201809:52:083rd Floor NorthDivision 01Joe Soap
Friday, 02 November 201810:58:373rd Floor NorthDivision 01Joe Soap
Friday, 02 November 201812:32:183rd Floor NorthDivision 01Joe Soap
Friday, 02 November 201813:07:083rd Floor NorthDivision 01Joe Soap
Friday, 02 November 201813:34:59Turnstile North 1 OUTDivision 02Jan Niemand
Friday, 02 November 201813:38:49Staff Parking OUTDivision 02Jan Niemand
Friday, 02 November 201813:39:09Staff Boom OUTDivision 02Jan Niemand

 

Need Help

5 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi jacosmit ,

    What is your desired reuslt for your formula? Could you please post your desired result if possible?

     

    Regards,
    Daniel He

    • jacosmit's avatar
      jacosmit
      Regular Visitor
      7 years ago

      Hi Daniel

       

      Something similar to the attached, per division.

      MONTHDIVISIONUSER_IDTIME ON SITERANKING
      Apr-19Division 01Joe Soap198:13:021
      Apr-19Division 01Frank Noble198:11:002
      Apr-19Division 01Stella Lange195:05:123
      Apr-19Division 01Frank Niemand185:00:234
      Apr-19Division 01Terry Crews156:00:155
      Apr-19Division 01Tom Cruise154:00:146
      Apr-19Division 01Rob Nelson150:10:157
      Apr-19Division 01Ted Mosley149:02:458
      Apr-19Division 01Sean Els148:00:159
      Apr-19Division 01Tom Brady147:15:3210
      • v-danhe-msft's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        Hi jacosmit ,

        So could you mean the TIME ON SITE and RANKING column is your desired column? How the logic about the TIME ON SITE column?

         

        Regards,

        Daniel He