Forum Discussion

BogdanVlad's avatar
BogdanVlad
Regular Visitor
2 years ago
Solved

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 e-mail title department log i...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
ray_aramburo
Super User
2 years ago

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.