Filtering Data to Determine User Access on Specific Dates

[MuFeR]

Hello, I'm a beginner in PowerBI and i got asked to try and create a couple reports. I'm not really sure what would be the correct terminology for what I'm trying to do so didn't have much luck when googling for something similar.

So the scenario is:
There's an existing application where all "access requests" go through and are stored in a database.
There are 3 types of requests (Grant access/modify access/revoke access).
The db looks like this (has lots of other stuff but they're irrelevant)

UserID	SystemID	RequestType	RequestCompletionDate
John	System 1	Grant access	01/01/2022
John	System 1	Revoke access	01/01/2023
John	System 1	Grant access	01/01/2024

Based on this info we can deduce that John had access on System 1 between 01/01/2022 and 31/12/2022, then didn't have access from 01/01/2023 until 31/12/2023 since his access was revoked on 01/01/2023, and now he currently has access as the latest request granted him access on 01/01/2024.

So the requirements are to create 3 pages:

1)A page where you can select a system from a slicer and see all users who currently have access on that system (my approach would be somehow to find all users who got a request for that system and where the latest RequestType is not "Revoke").
2)Pretty much same thing as #1 but for a specific date instead for today, for example see who had access on "System 1" on 05/05/2023.

3)Again same thing but for a time period, as in who had access on "System 1" at any point in 2023.

What I ended up doing was create a table, use the Group by wizard as shown below and keep only the rows where the type is not "Revoke" which covers requirement #1 but no idea how to approach the other 2.

[v-jialongy-msft · Accepted solution]

Hi @MuFeR 

For your second requirement, you can create the following measure to determine if the system was accessed on the specific date.

Today = IF(SELECTEDVALUE('Table'[RequestCompletionDate])=TODAY()&&SELECTEDVALUE('Table'[RequestType])="Grant access",1,0)

Then you can filter the value of measure in the filter.

Result:

For your third requirement you can use the following measure to determine who has access to the system for the duration of an entire year.

Time period = IF(COUNTROWS('Table')=1 && SELECTEDVALUE('Table'[RequestType])="Grant access",1,0)

You can then filter the corresponding values in the filter and work with the slicer to achieve the results you want.

Best Regards,

Jayleny

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

[lbendlin]

How many users?  How many entries in the access reference table?

You may not need to torture Power Query for that (except maybe to indicate From/To periods).

[MuFeR]

About 400 users and 10000 access records.

[lbendlin]

Can you refactor the access records into Grant - Revoke pairs  (with the last revoke being null for active access) ?

[MuFeR]

I'll give that a shot.