Count all hierarchy nodes including sum of items based on parent

[imyashy]

I have created hierarchy structure like below and I need to create a visualisation table that displays

1) list of parent nodes

2) total count of all nodes in the hierarchy for the parent (count should include the parent node too).

3) sum of other elements belonging to the child nodes (i.e. total users etc).

Any help would be greatly appreciated.

Expected result:

Parent Node	Total nodes	Total Users
PA	12	120
PB	6	49
PC	5	21
PD	3	22
PF	3	12

Diagram:

            PA
         /        \
       PB          PC
     /  |  \         /  \
  D  PD  E     G    PF
       /\                 /\
      I   J              K   L

Table structure:

childorg	Total users	parentorg	path	length
PA	50		1	1
PB	5	PA	PA|PB	2
PC	7	PA	PA|PC	2
D	10	PB	PA|PB|D	3
E	12	PB	PA|PB|E	3
PD	1	PB	PA|PB|PD	3
G	2	PC	PA|PC|G	3
PF	3	PC	PA|PC|PF	3
I	6	PD	PA|PB|PD|I	4
J	15	PD	PA|PB|PD|J	4
K	1	PF	PA|PC|PF|K	4
L	8	PF	PA|PC|PF|L	4

[imyashy]

Hi

I have managed to solve the problem by creating a new table and then using the following DAX measures:

total orgs in hierarchy = 
CALCULATE(
    COUNTROWS(orgs_levels),
    FILTER(
        orgs_levels,
        PATHCONTAINS(orgs_levels[Path], [hosting orgs])
            && orgs_levels[total users] <> 0
    )
) + 0

total users in hierarchy = 
CALCULATE(
    SUM(orgs_levels[total users]),
    FILTER(
        orgs_levels,
        PATHCONTAINS(orgs_levels[Path],[hosting orgs])
    )
)+0

For the second scenario (count parent and child orgs where category is not hosted) I created 2 seperate tables and then a 3rd via a Union (joining table 1 and 2).

[imyashy]

@lbendlin Apologies - I was preparing the above post late last night and realised a slight confusion between parentorg and hosting org.

I have corrected the above post including the pbix file and updated the wording regarding the logic for the expected outcome. The zip file contains:
- pbix file
- source data
- expected outcome

[lbendlin]

[imyashy]

@Ibendlin, Thank you - That works. Can you provide the measure for the non-host user count please?

Again, The measure works fine with a small list of parents however I have a total of 732 parents to run the measure on which results in a insufficient resources error..

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[imyashy]

@lbendlin 
No problem. Here is a link to a zip file containing the data, pbix file and example of expected outcome. I've provided as much detail as possible and tried to remain concise.

Many thanks for helping with this. The goal here is for the totals to appear for 731 1404 hosting orgs without facing a insufficient resources error (for reference I am using PowerBI on a laptop).

[danextian · Accepted solution]

Hi @imyashy 

Ad a new path column where 1 is replaced by the main parent

Create the following measures:

User count = 
VAR _parentOrg =
    SELECTEDVALUE ( tbl[parentorg] )
RETURN
    SUMX (
        FILTER (
            SUMMARIZECOLUMNS (
                tbl[path2],
                REMOVEFILTERS (),
                "@users", CALCULATE ( SUM ( tbl[Total users] ) )
            ),
            CONTAINSSTRING ( [Path2], _parentOrg )
        ),
        [@users]
    )

---------------------------------------------------------------------------

Node count = 
VAR _parentOrg =
    SELECTEDVALUE ( tbl[parentorg] )
RETURN
    COUNTROWS (
        (
            FILTER (
                SUMMARIZECOLUMNS (
                    tbl[path2],
                    REMOVEFILTERS (),
                    "@users", CALCULATE ( SUM ( tbl[Total users] ) )
                ),
                CONTAINSSTRING ( [Path2], _parentOrg )
            )
        )
    )

[lbendlin]

CONTAINSSTRING would need to include the pipe symbol to avoid mismapping, for example if you had nodes PC and PCB.

CONTAINSSTRING("|" & [path2] & "|","|" & [_parentorg] & "|")

Better use PATHCONTAINS.

[imyashy]

@lbendlin 

Good point. @danextian's Code works well with a small dataset however if there there is a child node which shares the same string of a parent node, it is included in the counts. i.e. Parent Node = PB, Child node = PBLPU. I updated the code with your recommendation which solved that issue.

How would the code be adapted to use PATHCONTAINS? If I replace CONTAINSSTRING with PATHCONTAINS the counts don't return any values.

As mentioned earlier, the code works well with a small dataset however I have to run the measures for 732 Parent nodes which result in an insufficient resources error (using my laptop). If I filter on approx 20 parent nodes, results are successfully returned. 

Can the code in the measures be optimised to use less resources?

[danextian]

As per testing, PATHCONTAINS is faster than CONTAINSSTRING. Using the same  pbix with parent not related to org_levels, try these:

User Count - PATHCONTAINS = 
VAR _parentOrg =
    SELECTEDVALUE ( Parents[parentorg] )
RETURN
    CALCULATE (
        SUM ( orgs_levels[total users] ),
        PATHCONTAINS ( orgs_levels[Path], _parentOrg )
    )


Record Count - PATHCONTAINS = 
VAR _parentOrg =
    SELECTEDVALUE ( Parents[parentorg] )
RETURN
    CALCULATE (
        COUNTROWS ( orgs_levels ),
        PATHCONTAINS ( orgs_levels[Path], _parentOrg )
    )

Faster but still not too fast. The only thing I can think of to make the calculations a lot faster is by precomputing a the value for each  path item - having a separate row for each path item.

PathItems = 
VAR _parents =
    DISTINCT ( orgs_levels[parentorg] )
RETURN
    FILTER (
        GENERATE (
            orgs_levels,
            VAR maxLen = orgs_levels[pathlength]
            RETURN
                ADDCOLUMNS (
                    GENERATESERIES ( 1, maxLen, 1 ),
                    "PathItem", PATHITEM ( orgs_levels[Path], [Value], TEXT )
                )
        ),
        [PathItem] IN _parents
    )

[imyashy]

I tried the updated DAX formulas with PATHCONTAINS. It works but not for all 1404 values.

What DAX formula are you using to create the 'value' column? 

When I try to create the PathItems column I receive the following error 'the expression refers to multiple columns. Multiple columns cannot be convered to a scalar value'

[danextian]

Pre-computing is done with either a column or a table. That formula is meant to create a calculated table.

GENERATESERIES creates a series of numbers with a default  column name called Value.