英文:
Create graph of user logins
问题
我有这样的数据：
在Power BI中。正如您所见，这是我的系统用户登录时的行。通常，如果用户注销然后重新登录，一个用户会有连续几行。我想创建一个图表，显示任何给定日期可能有多少人登录，可能按小时，但至少按天。有点像这样的东西：
（请原谅我的MS Paint技能）
嗯，列是'日期/时间'格式，就像我预期的那样，当我放在可视化上时，我得到了这个：
至少我希望能够绘制用户何时登录，但如果可能的话，我也希望考虑他们的'注销'时间，以显示任何给定时间当前登录的人数。
我肯定漏掉了一些明显的东西，（我需要先创建一个计数或其他什么吗？）但我会感激任何帮助！
英文:
I have data like this:
in Power BI. As you can see, it's rows of when users of my system logged in. Often one user will be several rows in a row, if they logged out and logged back in. I'd love to create a graph that kinda shows how many people are logged in on any given day, POSSIBLY by hour, but at least by day. Kinda thinking something like this:
(pardon my MS Paint skills)
Well, the columns are 'date/time' format like I'd expect, and when I put on a visual, I get this instead:
At the very least I'd love to chart when people logged in, but if possible I'd love to take into account their 'logout' time as well, to show volume of people currently logged in at any given time.
I'm sure I'm missing something obvious, (do I need to create a count or something first?) But would appreciate any help!
答案1
得分: 0
你需要创建一个日期和时间维度表。
一旦你有了这个表，确保它符合Power BI的要求，对于DirectQuery，它的行数必须小于100万行，对于导入，必须小于10GB等等。从这个链接了解更多信息。
现在，在你弄清楚了你的日期/时间维度表之后，你需要将登录时间和注销时间与该表连接起来。创建一个与我在图片中创建的登录和注销时间键类似的键，并将其与日期维度表的键连接起来。
最好在SQL中完成这项任务，当登录匹配时，分配1，当注销匹配时，分配0，并将其命名为开关（Switch）。然后使用日期维度中的日期作为X轴，开关作为Y轴，你将获得登录和注销信息的正弦波图表。这对于跟踪忘记打卡的用户非常有用。
对于工作时间，逻辑类似，但最后一步的计算不同。
英文:
You need to create a Date and Time Dimension Table.
Once you have this table, Make sure it fits the Power BI requirements, for DirectQuery it has to be less than 1 Million Rows, and for Import less than 10 GB, etc. Read up from this link.
Now after you've figured out your Date/Time Dimension table, you will need to join your Login Time and Logout time to this table. Create a Key similar to how I did in the Image for Login and LogOut time and join it to the Date Dimension table key.
Better to do this in SQL, when Login Matches, assign 1, when logout matches assign 0 and name it as Switch. then use the date from the date dimension as the x-axis and Switch as the Y-axis. you will get your sine wave graph for login and log-out information. Good to track users who forget to clock out.
For Hours worked the logic is similar, but the calculation is different in the final step.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论