How to get counts from related tables filtered by dates in both tables

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
A1          Test1   1       1/1/2020
A2          Test2   0       1/2/2020
A3          Test3   1       1/2/2020


B

ContactId   AccountID   ConFlag SysStartTime
C1          A1          1       1/1/2020
C2          A1          1       1/1/2020
C3          A1          0       1/1/2020
C4          A2          1       1/2/2020

I want to get the count of records in A that have 3 related records in B. I did this using a calculated column with the DAX:

 getcount = COUNTROWS(RELATEDTABLE(B))

And then created another calculated column to flag the one's with getcount = 3.

But the problem is that I want to check the count of records in A that have 3 related records in B at a given time. So I need to filter by the sysStartTime's in both the tables. For example, I want to get the count of records in A that have 3 related records in B as of 1/1/2010. So the result should be 1. Please advise on how I can do this using a Measure instead of a calculated column.

Alexis Olson · Accepted Answer · 2020-02-10 16:59:24Z

1

You should be able to do something like this:

SUMX ( A, IF ( COUNTROWS ( RELATEDTABLE ( B ) ) = 3, 1 ) )

Assuming you have a date table with a relationship to both A and B, a slicer on that date table will apply to the measure and you can set whatever dates you want.

answered Feb 10, 2020 at 16:59

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sindu_ Over a year ago

Thanks. I also want to filter by the AccFlag and ConFlag columns from both A and B. I can add the filter by the AccFlag but couldn't find a way to add the filter for ConFlag into the above DAX. Any suggestions for this?

Alexis Olson Over a year ago

You can use e.g. FILTER(A, A[AccFlag] = 1) and/or FILTER(B, B[ConFlag] = 1) instead of A and/or B, respectively.

Collectives™ on Stack Overflow

How to get counts from related tables filtered by dates in both tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related