Count Projects within ProjectStatus and ProjectType

[JensV]

Hi there.

I have a big challenge I hope you can help me with. I have a Table that track e.g. project status together with a lot other information about the project.

I would look like this:

Date	ProjectID	ProjectStatus	ProjectType
19-11-2017	100	Active	GroupeA
19-11-2017	200	Active	GroupeB
20-11-2017	300	Active	GroupeB
20-11-2017	100	Active	GroupeA
20-11-2017	200	On-Hold	GroupeB
.
.
19-12-2017	100	Closed	GroupeA

What I whould like to to is to count the number of Projects within Both ProjectStatus and ProjectGroup. It is however also, important that i only count the project once. By this means, if my date slice has the value 19-112017 By table would look like this:

ProjectTyps	Active	Closed	Onhold
GroupA	1
GroupB	1

However, if I select the whole time period 19-11-2017 - 19-12-2017 my table should look like this. 

ProjectTyps	Active	Closed	Onhold
GroupA		1
GroupB	1		1

I hope you can help me.

[technolog · Accepted solution]

To get the desired result, you can use DAX to create measures that perform these calculations. Here are the steps and DAX measures you can create.

Unique Projects:
Firstly, create a table that captures unique projects over the entire duration:
UniqueProjects = SUMMARIZE('TableName', 'TableName'[ProjectID], LASTNONBLANK('TableName'[Date], 1), 'TableName'[ProjectStatus], 'TableName'[ProjectType])
This formula creates a new table, UniqueProjects, which summarizes the original table by ProjectID, and captures the latest status and type for each unique project.

Active Projects:
Now, create a measure to count active projects:
Active Projects =
COUNTROWS(
FILTER(
UniqueProjects,
[ProjectStatus] = "Active"
)
)
This formula will count the rows in UniqueProjects where the ProjectStatus is "Active".

Closed Projects:
Similarly, create a measure to count closed projects:
Closed Projects =
COUNTROWS(
FILTER(
UniqueProjects,
[ProjectStatus] = "Closed"
)
)
On-Hold Projects:
Finally, create a measure to count the on-hold projects:
Onhold Projects =
COUNTROWS(
FILTER(
UniqueProjects,
[ProjectStatus] = "On-Hold"
)
)
Visualization:
Now, when you create a matrix or a table visualization in Power BI:
Drag ProjectType to Rows.
Drag the three measures Active Projects, Closed Projects, and Onhold Projects to Values.
This will show the counts in the desired format.

Remember, DAX context plays an important role. These measures will respect any filters or slicers you have on your report. So if you apply a date slicer or filter, the measures will only count the projects within that date range.

[skasper]

Hi,

you can get this view without any calculations, by using a matrix visual with 'ProjectType' as row elements and 'ProjectStatus' as column element and of course using 'Date' as a filter.

If you still need or want to calculate it manually, you could do it by defining a measure for each:

Active Project A = 
CALCULATE(
	COUNTA(Table1[ProjectID]);
	FILTER(Table1;
		Table1[ProjectStatus ] = "Active" &&
		Table1[ProjectType] = "GroupeA"
	)
)

Note that this is not a very efficient solution, as you would have to define one measure per status/project combination.

A better approach would be to just count the ProjectID and use filters. In that way you would have a single measure only:

Count Projects = COUNTA(Table1[ProjectID])

Hope this helps.

[JensV]

Thank you for the feedback. 

My challenge is that if a project has more than one 'Status' in the time period. The project will be counted for both Status'. However, I only want to count the most resent 'ProjectStatus' for each Project.