Counting number of volunteer who did more than one job each quarter

[DianaT]

Hi all,

I'm trying to visualise the count of volunteers who undertook more than one jobs in each quarter. My dataset is like the below.

Volunteer ID	Year-Quarter	JOB ID
1001	2019 Q1	A
1001	2020 Q1	A
1002	2020 Q1	B
1003	2019 Q4	C
1003	2020 Q1	C
1003	2020 Q1	D
1003	2020 Q2	C
1004	2020 Q1	E
1005	2019 Q4	F
1005	2019 Q4	G

And what I'm trying to achieve is to present the total count in a card, which interacts with a filter by Year-Quarter.

However all I managed so far is getting the count through a table visual... For instance, there is the table visual I get when I set Year-Quarter filter to 2020 Q1:

Volunteer ID	(Distinct) Count of Job ID	(Distinct) Count of Volunteer ID
1001	1	1
1002	1	1
1003	2	1
1004	1	1
Total	5	4

I then further filter this table by (Distinct) Count of Job ID >1

Volunteer ID	(Distinct) Count of Job ID	(Distinct) Count of Volunteer ID
1003	2	1
Total	2	1

And the total (Distinct Count of Volunteer ID) is what I want to visualise.

I'm pretty sure there is a more elegant way to do this via DAX. Any help/advice will be greatly appreciated.

Many thanks,

Diana

[Ashish_Mathur]

Hi,

Shouldn't the answer be 2 on a card visual - Volunteer ID 1003 and 1005?

[DianaT]

Hi Ashish,

Thanks for your message. ID 1005 should be counted in this case as I also have a filter in the scenerio "Year-Quarter" = 2020 Q1.

Diana

[HotChilli · Accepted solution]

I haven't given this much of a test.  See how you get on.

MeasureVVV = VAR _tab = SUMMARIZECOLUMNS(TableV[Volunteer ID], TableV[Year-Quarter],
                                             "DCount", DISTINCTCOUNT(TableV[JOB ID]))
RETURN
COUNTROWS(FILTER(_tab, [DCount] > 1))

[DianaT]

Thank you so much. This works beautifully.