Forum Discussion
Filtering Data to Determine User Access on Specific Dates
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.
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.
5 Replies
- lbendlin
Super User
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).
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.
