How to find out most frequent user based on the logged in period

[Anonymous]

Hi All,

I am looking for an answer to the below question I have while preparing some visuals in Power BI. Appreciate your help and valuable advice on this query.

I have data in a table as below:

I need to find out the most frequent and the least frequent user based on the duration they have logged into the device.

In this table, the most frequent user for the deviceId=1, would be User C and the least frequent user would be User A.

Similarly, the most frequent user for the deviceId=2, would be User Y and the least frequent user would be User X.

I am trying to find a way to calculate this in Power BI so that I can prepare some visuals based on that.

Thanks in advance for your help!.

[FreemanZ · Accepted solution]

Hi  @Anonymous 

try to create two measures with this code:

MaxUser = 
VAR MaxDuration = 
MAXX(
    TableName, 
    TableName[LastSeen] - TableName[FirstSeen]
)
RETURN
CALCULATE(
    VALUES(TableName[User]),
    TableName[LastSeen] - TableName[FirstSeen] = MaxDuration
)

MinUser = 
VAR MinDuration = 
MINX(
    TableName, 
    TableName[LastSeen] - TableName[FirstSeen]
)
RETURN
CALCULATE(
    VALUES(TableName[User]),
    TableName[LastSeen] - TableName[FirstSeen] = MinDuration
)

I tried and it worked like this:

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Column:

Difference = DATEDIFF([firstSeen],[lastSeen],MINUTE)

Measure:

Max user = 
CALCULATE (
    MAX ( 'Table'[User] ),
    FILTER (
        ALL ( 'Table' ),
        [Difference] = MAX ( 'Table'[Difference] )
            && [deviceID] = SELECTEDVALUE ( 'Table'[deviceID] )
    )
)

Min user = 
CALCULATE (
    MAX ( 'Table'[User] ),
    FILTER (
        ALL ( 'Table' ),
        [Difference] = MIN ( 'Table'[Difference] )
            && [deviceID] = SELECTEDVALUE ( 'Table'[deviceID] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[FreemanZ · Accepted solution]

Hi  @Anonymous 

try to create two measures with this code:

MaxUser = 
VAR MaxDuration = 
MAXX(
    TableName, 
    TableName[LastSeen] - TableName[FirstSeen]
)
RETURN
CALCULATE(
    VALUES(TableName[User]),
    TableName[LastSeen] - TableName[FirstSeen] = MaxDuration
)

MinUser = 
VAR MinDuration = 
MINX(
    TableName, 
    TableName[LastSeen] - TableName[FirstSeen]
)
RETURN
CALCULATE(
    VALUES(TableName[User]),
    TableName[LastSeen] - TableName[FirstSeen] = MinDuration
)

I tried and it worked like this: