Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RIDWIV
Helper I
Helper I

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. 

RIDWIV_0-1686032316943.png

RIDWIV_1-1686032335609.png

RIDWIV_2-1686032474929.png

 

 

2 ACCEPTED SOLUTIONS
v-stephen-msft
Community Support
Community Support

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)

vstephenmsft_0-1686199089785.png

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)

vstephenmsft_1-1686199249529.pngvstephenmsft_2-1686199255897.png

vstephenmsft_3-1686199262353.png

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.           

 

View solution in original post

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)

View solution in original post

11 REPLIES 11
v-stephen-msft
Community Support
Community Support

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)

vstephenmsft_0-1686199089785.png

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)

vstephenmsft_1-1686199249529.pngvstephenmsft_2-1686199255897.png

vstephenmsft_3-1686199262353.png

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.           

 

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)

Hello @v-stephen-msft , i tried but differences in time is not coming, please let me know what else I can do

RIDWIV_0-1686221742004.png

 

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.

vstephenmsft_1-1686279022786.png

 

 

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.

vstephenmsft_0-1686278448200.png

 

                                                                                                                                                         

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.           

Hii,

I found my mistake. I was applying formula on column intead of using measure tool. but still there is issue like i am not getting full value, please check

 

RIDWIV_0-1686301336871.png

 

 

I have done name formatting

please send me your mail id, i will send you the table.....and rank is working same as yours

Mahesh0016
Solution Sage
Solution Sage

@RIDWIV Please Share Your ENDOUTPUT For Better Understanding.

 

End output is not there

 

@RIDWIV No It's Not .

i mean it is not available in the table

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors