topic Calculate tasks in a store & its descendant departments in DAX Commands and Tips

Calculate tasks in a store & its descendant departments

Bassant_Saied — Wed, 06 Sep 2023 04:43:27 GMT

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!

Re: Calculate tasks in a store & its descendant departments

ERD — Wed, 06 Sep 2023 12:19:43 GMT

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:

Re: Calculate tasks in a store & its descendant departments

Bassant_Saied — Wed, 06 Sep 2023 14:15:55 GMT

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)
      )
)