Power BI Measure for Distinct Count

[Kavindya]

Hi, 

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. 

Required to get the Distinct count date and department wise to the below column. 

No fo Active Agents

Have to Add a filter as

Agent_Join.Department_ID = Summary.Department_ID

and 

Agent_Join.Date = Summary.Date

Agent Join

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

Summary

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

[v-yueyunzh-msft · Accepted solution]

Hi , @Kavindya 

According to your description, do you mean you want to add a column in "Summary" table to calculate the distincount of the [Agent ID] in 'Agent Join' table where Agent_Join.Department_ID = Summary.Department_ID and  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 && 'Agent Join'[Date]=_date) , "Agent ID",[Agent ID]))
return
COUNTROWS(_t)+0

Then we can get your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[djurecic]

Hi @Kavindya ,

 I'm not sure I fully understand the question, but DISTINCTCOUNT is the dax function you can use.

https://learn.microsoft.com/en-us/dax/distinctcount-function-dax