Forum Discussion

RIDWIV's avatar
RIDWIV
Helper I
3 years ago
Solved

Difference in time from one IP to another

Hello Power BI family, I want to find the diiffrence in time from one ip to another when any client id is chosen. Any solution how we can reach to this query.     
Data Protection
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi RIDWIV ,

     

    Here's my solution.

    1.Create a calculated column to get the rankings group by Client ID based on datetime.

    Rank = RANKX(FILTER('Table',[Client ID]=EARLIER('Table'[Client ID])),[Login Date & Time],,ASC,Dense)

    Then you can use DATEDIFF function to get the time intervals.

    Seconds = var _pre=CALCULATE(MAX('Table'[Login Date & Time]),FILTER(ALL('Table'),[Client ID]=MAX('Table'[Client ID])&&[Rank]=MAX('Table'[Rank])-1))
var _cur=MAX('Table'[Login Date & Time])
return DATEDIFF(_pre,_cur,MINUTE)

    When you use the slicer, the time interval from one IP to another in chronological order in the Client ID is displayed.  

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

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

     

  • RIDWIV's avatar
    RIDWIV
    3 years ago

    please cross check my formula 

    Rank = RANKX(FILTER('login_list',[clientid]=EARLIER('login_list'[clientid])),[loginDateTime],,ASC,Dense)
    Seconds = var _pre=CALCULATE(MAX('login_list'[loginDateTime]),FILTER(ALL(login_list),[clientid]=MAX('login_list'[clientid])&&login_list[Rank]=MAX(login_list[Rank])-1))
    var _cur=MAX(login_list[loginDateTime])
    return DATEDIFF(_pre,_cur,SECOND)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi RIDWIV ,

 

Does your rank column return the same result as mine?

Also you can modify the measure return _pre to check if there is a problem.

 

 

Judging by the screenshot, it seems that you might have more than one table. Isn't the clientid in your formula the same as the Client ID in the slicer? Similarly, isn't the clientid of your formula the same as the Client ID in the table visual? If possible, provide your data model, i.e. your tables and columns, and the relationships between tables. Please provide virtual data to protect your privacy.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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

RIDWIV's avatar
RIDWIV
Helper I
3 years ago

I have done name formatting