Measure Question

[Anonymous]

Hi, i am trying to create a measure to pupulate a teable but am struggling.

I have a quite comlicated datase, but have shown a simplified version below.

Projects Table

This contains a list of projects and programmes. each project and programme is assigned to a programme.

Change and Benefits Registers

These are sharepoint lists that users can capture records against projects or programmes. They are joined to the projects table by the Project ID.

Output needed

I am trying to create a 'programme report'. There will be  a slicer where the user selects 'Programme' from the unique values in the 'Projects[Programme].

I want to create a table that shows the

- count of changes

- count of changes open

- count of benefits

- count of benefits open

Any suggestions?

PS, summary of the schema below 

[v-jingzhang]

Hi @Anonymous 

You can create measures like below. Download the PBIX file for details.

Benefits = COUNT('Benefits Register'[Project ID])

Benefits Active = COUNTX(FILTER('Benefits Register','Benefits Register'[Benefit Status]="Active"),'Benefits Register'[Project ID])

Changes = COUNT('Change'[Project ID])

Changes Active = COUNTX(FILTER('Change','Change'[Change Status]="Active"),'Change'[Project ID])

 

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[PaulDBrown]

@Anonymous 

Any chance you can share the actual sample data, as opposed to an image?

[Anonymous]

Hi Paul, unoftunately i cant as it Microsoft Project Online data that we access through an api and also includes a lot of sensitive information, hence my representation in excel.