Measure to count transactions for all hierarchy levels

[Anonymous]

Please help me create a measure that would count how many transactions there are for a given period for each hierarchical level of accounts. I am really stuck right now.

There are two tables - transactions and accounts. Each acount has a parent account and a calculated path(account table[account name]) column.

I was trying something like this, but this only show if the account itself has made a transaction and not if its child accounts have made transactions. This measure will be in a matrix, where I can see how many transactions does each level have.

Count of Transactions =
VAR selPeriod = SELECTEDVALUE(period table[period])
VAR selAccount = SELECTEDVALUE(account table[account name])
VAR sumforAccount = SUMX(transactions table, IF(transactions table[period]=selPeriod && transactions table[account name]=selAccount,1,0))

RETURN
IF(sumforAccount>0,sumforAccount,0)

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think Path column should be an Enter Data. Path function is not suitable in your data model.

It is better for you to disconnect Account table and Transaction table or create an unrelated DimAccount table, relationship will impact measure.

Try this code.

Count =
VAR _SELECTACCOUNT =
    SELECTEDVALUE ( Account[Account Name] )
VAR _PATH =
    SUMMARIZE ( ALLSELECTED ( Account ), Account[Account Name], Account[Path] )
VAR _Search =
    FILTER (
        ADDCOLUMNS ( _PATH, "Search", SEARCH ( _SELECTACCOUNT, [Path],, 0 ) ),
        [Search] <> 0
    )
VAR _ACCOUNTLIST =
    FILTER (
        SUMMARIZE ( _Search, [Account Name] ),
        [Account Name] IN VALUES ( 'Transaction'[Account Name] )
    )
VAR _COUNT =
    COUNTAX ( _ACCOUNTLIST, [Account Name] )
RETURN
    _COUNT

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

The PATHCONTAINS function should be useful here.

See if something like this works:

Count of Transactions =
VAR selPeriod   = SELECTEDVALUE ( 'period table'[period] )
VAR selAccount  = SELECTEDVALUE ( 'account table'[account name] )
VAR subAccounts =
    CALCULATETABLE (
        VALUES ( 'account table'[account name] ),
        FILTER (
            ALL ( 'account table' ),
            PATHCONTAINS ( 'account table'[Path], selAccount )
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'transaction table' ),
        TREATAS ( subAccounts, 'transaction table'[Account Name] )
    )

[Anonymous]

Thank you, @AlexisOlson for your quick reply, but unfortunatelly this also just gives me count only for accounts that actually have the transaction and not the parent accounts.
I just can't wrapp my head around how to make the measure calculate: if a parent account is selected (selection is done from the accounts table side and not the transaction table) sum up all children account transactions.

[AlexisOlson]

If the account table is related to the transaction table on [Account Name], then try tweaking the TREATAS in my DAX code to

TREATAS ( subAccounts, 'account table'[Account Name] )

Can you share a sample pbix? The way the relationships are set up can make a big difference.