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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adamsli
Helper I
Helper I

Sum up connection time

Hi,

How to aggregate connection_time? I want to sum up the time of connection to every month. Do I need create a new measure?

adamsli_1-1606121756743.png

If so, how to write it down into DAX formula?

Styczeń means January and I want to sum up connection time to the one aggregated time value.

Like I have for instance in Excel: 

adamsli_2-1606122442953.png

22 connections with duration 27min and 12 seconds.

 

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @adamsli ,

 

We have checked your file. The issue is the Auto Date function.

You can close the Auto Date/Time function to solve this issue.

 

sum1.jpg

 

sum2.jpg

Best regards,

 

Community Support Team _ zhenbw

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

 

BTW, pbix as attached.

View solution in original post

10 REPLIES 10
v-zhenbw-msft
Community Support
Community Support

Hi @adamsli ,

 

We have checked your file. The issue is the Auto Date function.

You can close the Auto Date/Time function to solve this issue.

 

sum1.jpg

 

sum2.jpg

Best regards,

 

Community Support Team _ zhenbw

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

 

BTW, pbix as attached.

It's working. Thanks for help 🙂

v-zhenbw-msft
Community Support
Community Support

Hi @adamsli ,

 

Sorry for that we cannot download your pbix file.

When we click the download, it will jump to a new web page instead of downloading it.

 

s1.jpg

 

s2.jpg

 

Does your table structure like ours? And our subtraction’s type is Time.

 

s3.jpg

 

s4.jpg

 

If there is a gap between your data format and ours, you can paste the data in the reply, or you can tell me how to use the link you shared before.

 

Best regards,

 

Community Support Team _ zhenbw

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

Hi,

my subtraction in my case connection_time has format hour. In Power Query when I want to change to Duration type I'm receiving error:

adamsli_0-1606462176979.png

New link to my report: https://www.transfernow.net/yPpV9e112020

v-zhenbw-msft
Community Support
Community Support

Hi @adamsli ,

 

Where is your date column from?

Is it a column or a measure in red circle?

 

sum1.jpg

 

We add a date column and test it, then the result is fine.

 

sum2.jpg

 

Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

 

Best regards,

 

Community Support Team _ zhenbw

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

Data in the red circle are from the column in the table made in Power Query by subtracting the disconnect_hour column by the connection_hour column:

adamsli_0-1606392409585.png

Connection_time has data format: Hour.

new_meteorek is the name of the table.
new_meteorek[connection_time] is the column name from above in the picture.
 
I'm sending my report with removed confidential data like phone numbers or users name. Link below:
password: 202011
v-zhenbw-msft
Community Support
Community Support

Hi @adamsli ,

 

We assume that you already get the difference between connection and disconnection.

So we can create two measures to meet your requirement.

 

Duration = 
VAR TotalSeconds=SUMX('Table',HOUR('Table'[Subtraction])*3600+MINUTE('Table'[Subtraction])*60+SECOND('Table'[Subtraction]))
VAR Days = TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins = TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = MOD(TotalSeconds,60)
return IF(DAYS=0,"",IF(DAYS>1,DAYS&"days",Days&"day"))&IF(Hors<10,"0"&Hors,Hors)&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

 

Connection count = 
COUNT('Table'[M])

 

Sum1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

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

 

BTW, pbix as attached.

Count works perfectly: 

adamsli_2-1606291059610.png

 

but I have problem with Duration:

adamsli_0-1606290883023.png

Don't know why but I have zeros and becuase of this formula table shows data from 2015 to 2020 when I want in the filter only 2020.

Like I did in the slicer:

adamsli_1-1606291033870.png

 

adamsli
Helper I
Helper I

It's too complicated for me and I can't figure it out how to do it properly even with these tips topics.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.