Help with data shaping/validation

[BogdanVlad]

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	e-mail	title	department	log in time	log out time
johnd	johnd@example.com	end user	Quality Assurance	2024-01-16 06:40:32	2024-01-16 06:49:47
ericas	erica.s@example.com	manager	Transformation	2023-07-17 10:53:05
johnd	johnd@example.com	admin	Digitalization	2023-08-04 04:22:52	2023-08-04 04:23:49
jimb	jim.b@example.com	end user	Quality Assurance	2024-03-08 14:28:06
marks	marks@example.com	user	Marketing	2024-03-11 07:40:32
ericas	erica.s@example.com	manager	Transformation	2024-03-11 09:42:14	2024-03-11 09:49:10
johnd	johnd@example.com	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

[Anonymous · Accepted solution]

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 Xu

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

[ray_aramburo]

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.