Count ID based on Date Condition from different table with Many to Many Relationship

[PrathSable]

Hi,

I have 2 tables Team and Person, they are as follows

Team

Team _ID	Project	Start Date	End Date
1	ABC	01-01-2020	03-03-2020
1	DEF	03-02-2020	05-04-2020
2	EFG	06-06-2020	12-11-2020
2	HIJ	07-07-2020	10-10-2020

Person

Team _ID	Person_id	P_name	Date
1	101	Harry	02-02-2020
1	102	James	15-03-2020
2	104	Larry	07-08-2020
2	109	King	08-08-2020
1	103	Levin	07-07-2020
2	111	John	30-12-2020

What I need to do is count the distinct 'Person'[Person_Id] where the 'Person'[Date] falls between the 'Team'[Start Date] and 'Team'[End Date].
So the count will be 4, it will not count P_name Levin and John because the date 07-07-2020 & 30-12-2020 does not fall between Start Date & End Date for Team_ID 1 &2 Respectively in Teams table.

The relationship between the two tables is Many-Many, Also I've tried Min() & Max() functions it does not seem to give the right output.

Please advise.

[v-alq-msft · Accepted solution]

Hi, @PrathSable 

Based on your descirption, I created data to reproduce your scenario. The pbix file is attached in the end.

Team:

Person:

You may create a measure as below.

DistinctCountPerson = 
var tab = 
ADDCOLUMNS(
    ALL(Person),
    "Result",
    var _date = [Date]
    return
    IF(
        CALCULATE(
            COUNTROWS(Team),
            FILTER(
                'Team',
                _date>=[Start Date]&&_date<=[End Date]
            )
        )>0,
        1,0
    )
)
return
CALCULATE(
    DISTINCTCOUNT(Person[Person_id]),
    FILTER(
        tab,
        [Result]=1
    )
)

Result:

Best Regards

Allan

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

[amitchandak]

@PrathSable ,

Try

Measure = var _1 = SUMMARIZE(filter(Person, Person[Team _ID] =MAX(Team[Team _ID]) && Person[Date] >=MIN(Team[Start Date]) && Person[Date] <=max(Team[End Date])),Person[Person_id],Person[Team _ID]) return COUNTX(_1,[Person_id])

or

Measure = var _1 = SUMMARIZE(filter(Person, Person[Team _ID] =MAX(Team[Team _ID]) && Person[Date] >=MIN(Team[Start Date]) && Person[Date] <=max(Team[End Date])),Person[Person_id]) return COUNTX(_1,[Person_id])