Forum Discussion
Help with data shaping/validation
Hello guys,
I'm relatively new to PowerBi and I'm a little stuck.
I have an excel with raw data that contains data about users who acessed a tool:
| Username | title | department | log in time | log out time | |
| johnd | [email protected] | end user | Quality Assurance | 2024-01-16 06:40:32 | 2024-01-16 06:49:47 |
| ericas | [email protected] | manager | Transformation | 2023-07-17 10:53:05 | |
| johnd | [email protected] | admin | Digitalization | 2023-08-04 04:22:52 | 2023-08-04 04:23:49 |
| jimb | [email protected] | end user | Quality Assurance | 2024-03-08 14:28:06 | |
| marks | [email protected] | user | Marketing | 2024-03-11 07:40:32 | |
| ericas | [email protected] | manager | Transformation | 2024-03-11 09:42:14 | 2024-03-11 09:49:10 |
| johnd | [email protected] | admin | Digitalization | 2024-03-11 11:15:07 |
I need to take this into PowerBi and create a report that:
- will show how many times a user accessed the tool.
- create a visual showing how many users have accesed the tool each day, across each month.
- (optional) how much did they spent in the tool.
For the visuals, except the last one, I think I can manage to built them, but I'm stuck with the first requirement.
Apologies if this does not make sense, let me know if you need more info!
Thank you!
BV
Hi BogdanVlad
Your solution is great, ray_aramburo. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.
For your first and third questions, you can also do this:
First:
Create a measure as follows
times = CALCULATE(COUNT('Table'[Username]), ALLEXCEPT('Table', 'Table'[Username]))Result:
Third:
Create a measure as follows
spent = VAR _total = DATEDIFF(MAX([log in time]), MAX([log out time]), MINUTE) * 60 VAR _hours = QUOTIENT(_total, 3600) VAR _minutes = QUOTIENT(MOD(_total, 3600), 60) RETURN IF(MAX([log out time]) <> BLANK(), FORMAT(_hours, "[h]") & ":" & FORMAT(_minutes, "[m]"), BLANK())Result:
To make this formula more obvious, so I added a line of data "a".
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, you can drag the UserName column into a Values column (Y-axis for example) and automatically, since its a Text type column, Power BI will set it up as Count Of UserName and then changing it to Distinct Count in the field options:
This will give you a general view of the number of users per month, if you want a detailed view per user you would need to modify the Distinct Count to a Standard Count and add Username to the Legend field.
I have created those examples with the dummy data you provided. Check the attached file.
Hi BogdanVlad
Your solution is great, ray_aramburo. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.
For your first and third questions, you can also do this:
First:
Create a measure as follows
times = CALCULATE(COUNT('Table'[Username]), ALLEXCEPT('Table', 'Table'[Username]))Result:
Third:
Create a measure as follows
spent = VAR _total = DATEDIFF(MAX([log in time]), MAX([log out time]), MINUTE) * 60 VAR _hours = QUOTIENT(_total, 3600) VAR _minutes = QUOTIENT(MOD(_total, 3600), 60) RETURN IF(MAX([log out time]) <> BLANK(), FORMAT(_hours, "[h]") & ":" & FORMAT(_minutes, "[m]"), BLANK())Result:
To make this formula more obvious, so I added a line of data "a".
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
