Calculate count based on filter and if statement

[samroth]

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:

| summarize count() by BuildBranch, StageName

| extend trueCount_ = iff(BuildBranch != "develop", 1, count_)

Right now the summarize line has bin(Date, 1d) as well.

In the end I want to have a visual with a filter for the month of november that says:

R0	R1	R2
119	33	3

If I change that filter to October:

R0	R1	R2
98	1	0

If I change the filter to Oct-15 through Nov-15:

R0	R1	R2
99	33	0

 

[ryan_mayu · Accepted solution]

@samroth 

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