Need to count occurrences of each month for different columns

[BasMinnema90]

Hi, I'm new here and limited experience with Power BI,

I have a table which is only partially complete containing the month in which a number of different activities have been planned. Missing entries have "Unplanned" instead of a month. Not all activities take place for each ID - if not occuring, this is indicated with n/a.

What I have is something like this (but with hundreds of entries):

ID	Activity A	Activity B	Activity C	Activity D
1	January	n/a	January	March
2	February	n/a	March	Unplanned
3	Unplanned	Unplanned	n/a	Unplanned
4	January	n/a	n/a	August
5	March	February	Unplanned	June
6	Unplanned	Unplanned	n/a	n/a

What I need, is a table that counts the occurence of each month or "unplanned", for each activity, so:

Month	Activity A	Activity B	Activity C	Activity D
January	2	0	1	0
February	1	1	0	0
March	1	0	1	1
April	...	...	...	...
...
...
...
...
Unplanned	2	2	1	2

I tried creating a measure for each activity using calculate countrows, activity X = selectedvalue('date table'[month in calendar]), and putting the measures in columns of a table or matrix visual, along with month in calendar from my date table.

This seems to work, but only for the column/activity that has an active connection with the date table, and I can do that only for 1 column.

Can someone help me with a better way to do this?

[BasMinnema90]

Thank you @vicky_ , that seems to work!

But now the table displays the months in random order, do you know how I can make it sequential?

[BasMinnema90]

Nevermind, figured it out! Create index column with numbers, then at column tools select months colomn, sort by index column.

[vicky_ · Accepted solution]

You can generate a new table that has the month names + unplanned, which you can use instead of the month column in the date table. 

To create a new table: 

New Table = UNION(VALUES('Date Table'[Month in Calendar]), {"Unplanned"})

And then change your measure from above to:

Measure = calculate(countrows, activity X = selectedvalue('New table'[Column 1]))