Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I am aggregating data for development projects. These projects consist of different repositories, which give me metadata for timestamps when scanning the current status of a repository. (example of result table in answers)
repo.repositoryName | repo.uid | repo.loc | repo.scantimestamp | repo.overallRating | repo.locXOverallRating |
Kafka | 7918f57464e4595884652054b12f1735 | 136970 | 29.04.2019 14:30 | 2,79 | 480764,7 |
lgv | f0498d40214513effa4c415c83a161c6 | 111618 | 29.04.2019 14:27 | -0,23 | 558090 |
skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 | 29.04.2019 12:50 | 3,68 | 55624,5 |
hbase | b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 29.04.2019 12:35 | 1,58 | 446155,44 |
Kafka | 7918f57464e4595884652054b12f1735 | 136970 | 29.04.2019 12:29 | 2,78 | 495831,4 |
Kafka | 7918f57464e4595884652054b12f1735 | 136970 | 18.04.2019 17:54 | 2,23 | 398582,7 |
angular TS | b40909083e1a671f8a9a16f51a2fffa1 | 86183 | 18.04.2019 12:39 | 3,96 | 430915 |
three | 56c65bdac0fd8f8797d3e0ae7bf261e0 | 1521 | 18.04.2019 12:39 | 1,9 | 7605 |
Visual Studio | 59316bad6ca2c1874312a351ef418413 | 105533 | 18.04.2019 09:12 | 2,13 | 525554,34 |
three | 56c65bdac0fd8f8797d3e0ae7bf261e0 | 1521 | 18.04.2019 09:11 | 1,87 | 7605 |
hbase | b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 16.04.2019 15:16 | 1,73 | 457037,28 |
skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 | 16.04.2019 15:15 | 3,68 | 55624,5 |
skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 | 16.04.2019 15:13 | 3,68 | 55624,5 |
hbase | b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 16.04.2019 15:02 | 2,07 | 433913,37 |
hbase | b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 12.04.2019 10:10 | 1,7 | 429832,68 |
skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 | 12.04.2019 10:06 | 2,29 | 55253,67 |
hbase | b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 12.04.2019 09:55 | 1,97 | 412149,69 |
skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 | 12.04.2019 09:54 | 2,29 | 55253,67 |
hivemall(incubating) | 7a85ba7f87011d452677ec51686f0e2c | 69258 | 12.04.2019 09:50 | 2,77 | 304735,2 |
skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 | 12.04.2019 09:45 | 2,91 | 53275,91 |
hivemall(incubating) | 7a85ba7f87011d452677ec51686f0e2c | 69258 | 12.04.2019 09:43 | 2,82 | 307505,52 |
skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 | 12.04.2019 09:39 | 1,92 | 38566,32 |
hivemall(incubating) | 7a85ba7f87011d452677ec51686f0e2c | 69258 | 12.04.2019 09:37 | 2,8 | 306120,36 |
skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 | 12.04.2019 09:19 | 2,95 | 54264,79 |
hivemall(incubating) | 7a85ba7f87011d452677ec51686f0e2c | 69258 | 12.04.2019 09:18 | 2,94 | 310275,84 |
lgv | f0498d40214513effa4c415c83a161c6 | 111618 | 10.04.2019 17:20 | -0,23 | 558090 |
ol-cesium | b5b06cbae1ecb58dc86bcebcf99bb8bf | 4911 | 10.04.2019 16:52 | 2,34 | 24555 |
lgv | f0498d40214513effa4c415c83a161c6 | 111618 | 10.04.2019 15:28 | -0,31 | 558090 |
Kafka | 7918f57464e4595884652054b12f1735 | 136970 | 09.04.2019 20:52 | 2,92 | 512267,8 |
Kafka | 7918f57464e4595884652054b12f1735 | 136970 | 09.04.2019 20:33 | 2,24 | 399952,4 |
Then I have another Table for the projects and their consisting repos. Including sumloc what is the size of the complete project and repoloc what is the size of the referenced repo.
projectdetails.sumloc | projectdetails.projectId | projectdetails.repositoryCount | projectdetails.projectName | repositoryId | repositoryName | uid | projectdetails.repoloc |
116529 | 2 | 2 | GeoMaster | 3 | lgv | f0498d40214513effa4c415c83a161c6 | 111618 |
116529 | 2 | 2 | GeoMaster | 4 | ol-cesium | b5b06cbae1ecb58dc86bcebcf99bb8bf | 4911 |
136970 | 1 | 1 | Kafka | 1 | Kafka | 7918f57464e4595884652054b12f1735 | 136970 |
217642 | 3 | 3 | Trends | 8 | hbase | b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 |
217642 | 3 | 3 | Trends | 5 | hivemall(incubating) | 7a85ba7f87011d452677ec51686f0e2c | 69258 |
217642 | 3 | 3 | Trends | 6 | skywalking (incubating) | 287505f3cf5bbb213de83cd54f76a5be | 12361 |
I have in repo.overallRating values between 5 and -5 for the repositories for different timestamps.
Since I just get a new scan/timestamp if any changes are done in a repository I might only get a timestamp of some repo every x month, while the rest of the repositories are constantly creating timestamps.
Repo.loc is the size.
Repo.overall is the score.
I want to SUM(repo.overallRating*repo.loc)/projectdetailssumloc) in each project. But it has to just take the last timestamps into account available.
So that a project consisting of repos a, b and c the last timestamp of each of these repos is used in the formula.
By this I get a weighted (after size) overall.rating for my projects that I can plot over a timeline, for trend analytics.
Thank you for your help community. I am struggling with this since hours....
Any chance you could post what your expected output would be from the data provided?
@Greg_Deckler Of course, my apologies.
In the meanwhile, I created a right join to connect project data with repo timestamp data.
So all fields you see here are available (more but that is just noise for this topic)
This would be the output for the Project named Trends
It is filtered on Trends and should ignore the other 4 projects and vice versa. For this I created the column
repositories.
repo.uid+project.id
That is unique in each project, allowing two projects to use the same repositories. So
repositories.
repo.uid+project.id
we can use for a DISTINCT Filter and I guess we can use an EARLIER to get those numbers together. It is a accumulated Value like here https://community.powerbi.com/t5/Desktop/Accumulate-added-value/td-p/82382 but I need it to MAX it to the number of unique values of
repositories.
repo.uid+project.id that are above it.
Similar to this https://stackoverflow.com/questions/51353339/measure-to-sum-of-more-recent-values-using-dax-from-pow...
I wrote down the formular for each line in the RollingOverallRating in Excel (scroll right)
project details. projectId | project details. sumloc | project details. repository Count | project details. project Name | repositories. repo. repositoryName | repositories. repo.uid+project.id | repositories. repo.loc | repositories. repo. scantimestamp | repositories. repo.overallRating | Rolling Overallrating |
3 | 227661 | 3 | Trends | hivemall(incubating) | 37a85ba7f87011d452677ec51686f0e2c | 69258 | 12.04.2019 09:18 | 2,94 | ((2,94*69258))/(69258) |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 12.04.2019 09:19 | 2,95 | ((2,94*69258)+(2,95*22380))/(69258+22380) |
3 | 227661 | 3 | Trends | hivemall(incubating) | 37a85ba7f87011d452677ec51686f0e2c | 69258 | 12.04.2019 09:37 | 2,8 | ((2,80*69258)+(2,95*22380))/(69258+22380) |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 12.04.2019 09:39 | 1,92 | ((2,80*69258)+(1,92*22380))/(69258+22380) |
3 | 227661 | 3 | Trends | hivemall(incubating) | 37a85ba7f87011d452677ec51686f0e2c | 69258 | 12.04.2019 09:43 | 2,82 | ((2,82*69258)+(1,92*22380))/(69258+22380) |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 12.04.2019 09:45 | 2,91 | ((2,82*69258)+(2,91*22380))/(69258+22380) |
3 | 227661 | 3 | Trends | hivemall(incubating) | 37a85ba7f87011d452677ec51686f0e2c | 69258 | 12.04.2019 09:50 | 2,77 | 2,652773522 |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 12.04.2019 09:54 | 2,29 | ((2,77*69258)+(2,29*22380))/(69258+22380) |
3 | 227661 | 3 | Trends | hbase | 3b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 12.04.2019 09:55 | 1,97 | ((2,77*69258)+(2,29*22380)+(1,97*136023))/(69258+22380+136023) |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 12.04.2019 10:06 | 2,29 | ((2,77*69258)+(2,29*22380)+(1,97*136023))/(227661) |
3 | 227661 | 3 | Trends | hbase | 3b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 12.04.2019 10:10 | 1,7 | ((2,77*69258)+(2,29*22380)+(1,7*136023))/(227661) |
3 | 227661 | 3 | Trends | hbase | 3b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 16.04.2019 15:02 | 2,07 | ((2,77*69258)+(2,29*22380)+(2,07*136023))/(227661) |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 16.04.2019 15:13 | 3,68 | ((2,77*69258)+(3,68*22380)+(2,07*136023))/(227661) |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 16.04.2019 15:15 | 3,68 | ((2,77*69258)+(3,68*22380)+(2,07*136023))/(227661) |
3 | 227661 | 3 | Trends | hbase | 3b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 16.04.2019 15:16 | 1,73 | ((2,77*69258)+(3,68*22380)+(1,73*136023))/(227661) |
3 | 227661 | 3 | Trends | hbase | 3b90a7ac3118f6ecfa4537f1ab359bc5b | 136023 | 29.04.2019 12:35 | 1,58 | ((2,77*69258)+(3,68*22380)+(1,58*136023))/(227661) |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 29.04.2019 12:50 | 3,68 | 2,14845494 |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 29.04.2019 18:05 | 3,57 | 2,137641493 |
3 | 227661 | 3 | Trends | skywalking (incubating) | 3287505f3cf5bbb213de83cd54f76a5be | 22380 | 30.04.2019 09:56 | 3,63 | 2,143539737 |
The unfiltered table has of course more projects. But to much chars for this post.
*edit, table had been a row off
Thanks in advance!
Greets
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |