Count Records If Record Based on Records in Another Table and Own Table

[Anonymous]

I have two tables:

Incidents

Number	Created_At
0000	07/01/2020
1111	08/01/2020
2222	08/05/2020
3333	08/15/2020

Incident Statistics

Number	Statistic_Type	Value
0000	to_assignment	07/05/2020
1111	to_assignment	08/01/2020
1111	to_resolve	08/18/2020
2222	to_assignment	08/05/2020
2222	to_resolve	08/25/2020
3333	to_assignment	08/15/2020

I need to show how many 'incidents' where "Open" at the beginning of a period.

So, if my slicer was set to look at the period 8/10 - 8/20, I would expect to see 3 Open Incidents. This is because only incident numbers 0000, 1111, 2222 where open on 8/10 (and resolved after 8/10). 

** Note: If an incident is still open, it will not have a "to_resolve" record so would still be counted.

[Anonymous · Accepted solution]

Hi @Anonymous ,

1.Create a calendar table.

Calendar = CALENDAR(DATE(2020,7,1),DATE(2020,8,31))

2.Combine the two tables with Merge in the Power Query Editor and expand the columns.

3.Create a calculated column.

Column = CALCULATE( COUNTROWS('Merge1'),ALLEXCEPT(Merge1,Merge1[Number]))

4.Create a measure to count.

Count = 
VAR count1 =
    CALCULATE (
        COUNTROWS ( 'Merge1' ),
        FILTER ( 'Merge1', [Column] = 1 && MIN ( 'Calendar'[Date] ) >= [Created_At] )
    )
VAR count2 =
    CALCULATE (
        DISTINCTCOUNT ( Merge1[Number] ),
        FILTER (
            'Merge1',
             (
                MIN ( 'Calendar'[Date] ) >= [Created_At]
                    && MIN ( 'Calendar'[Date] ) <= [Value]
                    && [Column] = 2
            )
        )
    )
RETURN
    count1 + count2

5.The result is as follows.

You can check more details from here.

Best Regards,
Stephen Tao

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

[Greg_Deckler]

@Anonymous - If I understand this correctly, make sure that you have a disconnected slicer and then you could do something like the following:

Measure =
  VAR __MinDate = MIN('Slicer'[Date])
  VAR __Table = 
    ADDCOLUMNS(
      FILTER('Incidents',[Created_At]<=__MinDate),
      "Closed",IF("to resolve" IN SELECTCOLUMNS(RELATEDTABLE('Incident Statistics'),"Closed",[Statistic_Type]),1,0)
    )
RETURN
  COUNTROWS(__Table,[Closed]=0)

[lbendlin]

Have you tried using LASTNONBLANKVALUE() ?

[Anonymous]

Hi @lbendlin ,

Can you please elaborate on how I would use that function to get a count based on multiple conditions/tables?

Thanks!