Forum Discussion

PrathSable's avatar
PrathSable
Advocate II
5 years ago
Solved

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

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...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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])