CODEWITHSUNDEEP
powerbidax
Sindu_
Sindu_

Reputation: 1455

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

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.

Upvotes: 0

Views: 68

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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.

Upvotes: 1

Related Questions