Count of referenced table rows count

[kish14all]

Hi,

I have two tables, Projects and Application (1-* relation, One project contains many Applications) and Applications Table has a column IsActive which will be true if the Application is Active else false. Now I need to add a new column IsActive to Projects table such that it will be true when anyone Application is Active and false when all Applications are not Active. I am able to get the total Active projects count using Measure but I can't filter or use the slicer on a Measure so want to add a new column in Projects table.

[harshnathani]

Hi @kish14all ,

Not sure why you want a column in Project Table. You can create a Column in Application Table and then pull the values from Project and this column in a visual.

Create a Calculated Column

AllAct =

var _a = COUNTROWs(FILTER(Applications,Applications[Projects] = EARLIER(Applications[Projects]) && Applications[IsActive] = TRUE()))

RETURN

IF (_a>1 ,"True","False")

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[kish14all]

Hi @harshnathani,

Thanks a lot for the response, this is working fine, the reason why I want a column in Projects Table because I have another Organizations Table which having relations to Projects Table (1-* relation) and needs to Apply the same logic of list of Organizations and IsActive ( true - if having at least one Active Project and false when all Projects are not Active) but the Organization Table don't have a direct relation with Applications Table. below is the structure of the tables.

The column I need at Organization level and Project level so that I can use that in the slicer to filter the number of active inactive Projects and Application's view.

Organization Table

Identifier	Name	<Required Column> Have any Active Projects
1	Org1
2	Org2
3	Org3
4	Org4

Projects Table

Identifier	Organization.Identifier	name	<Required Column> IsActive
1	2	Prj1
2	2	Prj2
3	1	Prj2
4	3	Prj1

Application Table

Identifier	Project.Identifier	name	IsActive
1	3	App1	True
2	3	App2	False
3	1	App3	False
4	2	App1	True
5	4	App3	False