Calculate tasks in a store & its descendant departments

[Bassant_Saied]

Hi,

I have two tables one contains tasks & other has store & departments linked by parent_id column

for example:

Store table:

Name	ID	Parent ID	Type
Store 1	1		Store
Department A	2	1	Department
Department B	3	1	Department

Task Table:

Name	ID	OwningStoreID	Status
Task A	1	1	Open
Task B	2	2	Completed
Task C	3	3	Open

I want to create a measure to count all tasks opened for a store & all tasks open in the descendant departments. Which means to show count 3 for store 1

How could I do this? Thanks! 

[Bassant_Saied]

Thanks @ERD  for your solution.

Actually there is also another simpler solution using Path functions. I created a ne column called PathStore with has the Path value for each store & his parents. My problem was whenever store is filtered the count of tasks rows are filtered too. So I use this formula:

VAR SelectedStore = SELECTEDVALUE ( task[owningStoreID] )

VAR TotalTasks = CALCULATE (
     COUNTROWS(task),
     FILTER(ALL(task), 
        PATHCONTAINS (task[PathStore], SelectedBU)
      )
)  

[ERD · Accepted solution]

Hi @Bassant_Saied , one of the ways is to use hierarchies. For that a number of calculated columns and measures need to be created.

Calculated columns in the Store table:

FullPath = PATH( Store[ID], Store[Parent ID])

Level1 = LOOKUPVALUE(Store[Name], Store[ID], PATHITEM(Store[FullPath], 1, INTEGER))

Level2 = IF(PATHLENGTH(Store[FullPath]) >= 2, LOOKUPVALUE(Store[Name], Store[ID], PATHITEM(Store[FullPath], 2, INTEGER)), Store[Level1])

NodeDepth = PATHLENGTH(Store[FullPath])

IsLeaf =
VAR c_id = Store[ID]
VAR depsAtParentLevel = CALCULATE ( COUNTROWS ( Store ), ALL ( Store ), Store[Parent ID] = c_id )
RETURN
    depsAtParentLevel = 0

Measures:

tasks = COUNTROWS(Task)

Tasks amt =
VAR check = MAX ( Store[NodeDepth] ) + 1 < [BrowseDepth]
VAR extraLevel = MAX ( Store[NodeDepth] ) + 1 = [BrowseDepth]
VAR amt = [tasks]
VAR hasData = NOT ISBLANK ( amt )
VAR leaf = SELECTEDVALUE ( Store[IsLeaf], FALSE () )
VAR result = IF ( NOT check, IF ( extraLevel, IF ( NOT leaf && hasData, amt ), amt ) )
RETURN
    IF ( MAX ( Store[NodeDepth] ) < [BrowseDepth], BLANK (), result )

If you want to show the amount of tasks per store itself as well:

Tasks amt =
VAR check = MAX ( Store[NodeDepth] ) + 1 < [BrowseDepth]
VAR extraLevel = MAX ( Store[NodeDepth] ) + 1 = [BrowseDepth]
VAR amt = [tasks]
VAR hasData = NOT ISBLANK ( amt )
VAR leaf = SELECTEDVALUE ( Store[IsLeaf], FALSE () )
VAR result = IF ( NOT check, IF ( extraLevel, IF ( NOT leaf && hasData, amt ), amt ) )
RETURN
    result