Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |