Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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:
22 connections with duration 27min and 12 seconds.
Solved! Go to Solution.
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.
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.
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.
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 🙂
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.
Does your table structure like ours? And our subtraction’s type is Time.
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:
New link to my report: https://www.transfernow.net/yPpV9e112020
Hi @adamsli ,
Where is your date column from?
Is it a column or a measure in red circle?
We add a date column and test it, then the result is fine.
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:
Connection_time has data format: Hour.
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])
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:
but I have problem with Duration:
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:
It's too complicated for me and I can't figure it out how to do it properly even with these tips topics.
@adamsli , In case you want to sum up duration, refer to these blogs
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |