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 _IDProjectStart DateEnd Date
1ABC01-01-202003-03-2020
1DEF03-02-202005-04-2020
2EFG06-06-202012-11-2020
2HIJ07-07-202010-10-2020

 

Person

 

Team _IDPerson_idP_nameDate
1101Harry02-02-2020
1102James15-03-2020
2104Larry07-08-2020
2109King08-08-2020
1103Levin07-07-2020
2111John30-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.

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.

2 Replies

  • 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])
  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.