Forum Discussion
Calculate count based on filter and if statement
I have a table with columns for branchName, stageName, date, and count of how many times that day a build from that branch successfully executed that stage.
Something like this:
| BuildBranch | StageName | Date | Count |
| develop | R0 | 2020-11-25 | 89 |
| develop | R0 | 2020-11-26 | 38 |
| snap/1016 | R0 | 2020-11-05 | 1 |
| snap/1016 | R0 | 2020-11-03 | 1 |
| snap/1017 | R0 | 2020-11-04 | 1 |
| develop | R0 | 2020-10-25 | 97 |
| snap/1016 | R0 | 2020-10-14 | 1 |
| develop | R1 | 2020-11-06 | 32 |
| snap/1016 | R1 | 2020-11-09 | 1 |
| snap/1016 | R1 | 2020-10-29 | 1 |
| snap/1016 | R1 | 2020-10-28 | 1 |
| develop | R2 | 2020-11-27 | 3 |
I want to create a visual that displays the total count of successes for a given stage during a given time period with a caveat.
Successes from the "develop" branch are unique but successes from any other branch should be treated as duplicates. For example, if I am calculating successes for Stage "R0" in the month of November, the calculation should be something of the sort: 89 + 38 + 1 + 1 (89 unique successes from develop on 2020-11-25, 38 unique successes from develop on 2020-11-26, 1 success in total from snap/1016, 1 success total from snap/1017).
I want to be able to change the date filter for the whole dashboard at any given time. If I didn't need to change the date filter, I would just change my initial query (in Kusto) to this:
| R0 | R1 | R2 |
| 119 | 33 | 3 |
| R0 | R1 | R2 |
| 98 | 1 | 0 |
| R0 | R1 | R2 |
| 99 | 33 | 0 |
is there a logic that sna's count is always 1? if so you can try this.
Measure = sumx(FILTER('Table','Table'[BuildBranch]="Develop"),'Table'[Count])+CALCULATE(DISTINCTCOUNT('Table'[BuildBranch]),FILTER('Table','Table'[BuildBranch]<>"Develop"))please see the attachment