Power BI Measure for Distinct Count

Question

Hi,&nbsp;I have two tables calling Agent Join Table and Summary Table. So I required to identify how many agents activated from the particular department and date wise.&nbsp;&nbsp;Required to get the Distinct count date and department wise to the below column.&nbsp;No&nbsp;fo&nbsp;Active&nbsp;Agents&nbsp;Have to Add a filter asAgent_Join.Department_ID = Summary.Department_IDand&nbsp;Agent_Join.Date = Summary.Date&nbsp;Agent&nbsp;Join&nbsp;&nbsp;Department&nbsp;IDDateAgent&nbsp;ID112/12/20221000001113/12/20221000002114/12/20221000003115/12/20221000004212/12/20221000001212/12/20221000002213/12/20221000003312/12/20221000001313/12/20221000009313/12/20221000010412/12/20221000001413/12/20221000002415/12/20221000013&nbsp;&nbsp;Summary&nbsp;&nbsp;&nbsp;&nbsp;DateDepartmetnIDDepartment&nbsp;NameNo&nbsp;fo&nbsp;Agents&nbsp;Assigned&nbsp;to&nbsp;DepartmentNo&nbsp;fo&nbsp;Active&nbsp;Agents12/12/20221ABC3&nbsp;12/12/20222CDA6&nbsp;12/12/20223AHN7&nbsp;12/12/20224NDH5&nbsp;13/12/20221ABC3&nbsp;13/12/20222CDA6&nbsp;13/12/20223AHN7&nbsp;13/12/20224NDH5&nbsp;14/12/20221ABC3&nbsp;14/12/20222CDA6&nbsp;14/12/20223AHN7&nbsp;14/12/20224NDH5&nbsp;15/12/20221ABC3&nbsp;15/12/20222CDA6&nbsp;15/12/20223AHN7&nbsp;15/12/20224NDH5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-yueyunzh-msft · Accepted Answer

Hi ,&nbsp;Kavindya&nbsp;
According to your description, do you mean you want to add a column in "Summary" table to calculate the distincount of the [Agent&nbsp;ID] in 'Agent&nbsp;Join' table where&nbsp;Agent_Join.Department_ID = Summary.Department_ID and&nbsp; Agent_Join.Date = Summary.Date?
If this , here are the steps you can refer to :(1)My test data is the same as yours.
(2)We can click "New column" and enter:
No fo Active Agents = var _date = [Date]
var _departmentID=[DepartmetnID]
var _t =DISTINCT(SELECTCOLUMNS( FILTER('Agent Join','Agent Join'[Department ID]=_departmentID &amp;&amp; 'Agent Join'[Date]=_date) , "Agent ID",[Agent ID]))
return
COUNTROWS(_t)+0
Then we can get your need:

&nbsp;
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!&nbsp;
&nbsp;
Best Regards,
Aniya Zhang
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly

Forum Discussion

Power BI Measure for Distinct Count

2 Replies

Related content

POWER BI DAX MEASURE

count distinct

power bi measure

Power Query Distinct non repeating Index

Power BI Measure - Summarize distinct values


Department ID	Date	Agent ID
1	12/12/2022	1000001
1	13/12/2022	1000002
1	14/12/2022	1000003
1	15/12/2022	1000004
2	12/12/2022	1000001
2	12/12/2022	1000002
2	13/12/2022	1000003
3	12/12/2022	1000001
3	13/12/2022	1000009
3	13/12/2022	1000010
4	12/12/2022	1000001
4	13/12/2022	1000002
4	15/12/2022	1000013


Date	DepartmetnID	Department Name	No fo Agents Assigned to Department	No fo Active Agents
12/12/2022	1	ABC	3
12/12/2022	2	CDA	6
12/12/2022	3	AHN	7
12/12/2022	4	NDH	5
13/12/2022	1	ABC	3
13/12/2022	2	CDA	6
13/12/2022	3	AHN	7
13/12/2022	4	NDH	5
14/12/2022	1	ABC	3
14/12/2022	2	CDA	6
14/12/2022	3	AHN	7
14/12/2022	4	NDH	5
15/12/2022	1	ABC	3
15/12/2022	2	CDA	6
15/12/2022	3	AHN	7
15/12/2022	4	NDH	5