How to filter by two related tables in the SUMX function in DAX

Question

I have two tables A and B as shown below. The AccountID in A has a relationship with the AccountID in B.

A

AccountID   CmpName AccFlag SysStartTime  sysEndTime
 A1          Test1   1       1/1/2020      12/31/9999
 A2          Test2   0       1/2/2020      12/31/9999
 A3          Test3   1       1/2/2020      12/31/9999


B

ContactId   AccountID   ConFlag SysStartTime   SysEndTime
C1          A1          1       1/1/2020        12/31/9999
C2          A1          1       1/1/2020        12/31/9999
C3          A1          0       1/1/2020        12/31/9999
C4          A2          1       1/2/2020        12/31/9999

I want to get the count of records in A that have 3 or more related records in B. I also want to get the count filtered by the Accflag, conflag, sysStartTime and sysEndTime from both the tables. I have the following DAX and it gives me the count of records in A that have 3 or more related records in B filtered by the Accflag, sysStartTime and sysEndTime of A. I want to add the filtering with ConFlag, sysStartTime and sysEndTime as well but I'm not sure how to add it to the following DAX. Please help.

 SUMX ( A, 
 IF ( COUNTROWS ( RELATEDTABLE ( B ) ) >= 3 && 
 A[Accflag]=1 && 
 A[SysStartTime]>=TODAY() &&  
 A[SysEndTime]>= VALUE("12/31/9999"),1 )
 )

Answer 1

I think the easiest way to do this would be to create a calculated column which indicates whether each row passes the check or not. Something like the below might work:

Ind = 
    VAR AccountID=A[AccountID]
    VAR Count1 = CALCULATE(COUNTROWS(B),FILTER(B,B[AccountID]=AccountID))
RETURN IF(Count1>=3 && A[Accflag]=1 && A[SysStartTime]>=TODAY() && A[SysEndTime]>= VALUE("12/31/9999"),1,0)

Ind will give out 0 or 1 for each row and then you can simply sum up the field to get the total number of rows which meet each criteria. This will be useful, in case you need to add further conditions to the calculation. Hope this helps.

Answer 2

You could do it like this:

Go to the query editor and add a blank query. Refer this blank query (lets call this query TableBGrouped) to your TableB:

 = TableB

Now apply the following step:

The relationship will look now like this:

Add a measure to TableBGrouped:

3 or more Count = CALCULATE(COUNT(TableBGrouped[AccountID]); TableBGrouped[Count] > 2)

Add filter and you got your result:

You can now apply filter: