I know recursive measures have been discussed a few times in the Forum, but none of the solutions solve this issue:
First of all we are operating in Direct Query Mode:
We have a Ledger Table with Entries like:
| Account | Value |
| 111 | 23 |
| 111 | 56 |
| 112 | 13 |
| 555 | 27 |
And we have an Account table like:
| Acc | Parent |
| 1 | |
| 11 | 1 |
| 111 | 11 |
| 112 | 11 |
| 55 | 5 |
| 555 | 55 |
What I now try to achieve is to summarize the Values on their respective Account Levels.
My Idea was to create a Measure in the Account Table like:
Account.Amount = SUM(Ledger.Value)
I have this, and it works fine, but what I would now need to cover the higher accounts in the hierarchy is:
Account.Amount = SUM(Ledger.Value) + CALCULATE(SUM(ChildAccount.Amount), ChildAccount.Parent = ThisAccount.Acc)
Or in Words: When I am on a Child Record, summarize the Value in the Ledger,
If I am on a parent Record, summarize the Value in the Ledger, and add the (summarized Value) from the Child Accounts.
And I need to be able to do this recursive Measure through 3-9 Account Measure Levels?
If DAX would be a programming Language that would not be a problem, but esp. with Direct Queries in Mind, I know it is not that easy.
I don't know if that helps, but on option that came to my mind was, to have kind-of a lookup funtion for all Sub-Level Accounts, and then create a generic filter on the Ledger like CALC(SUM(Ledger.Value), Filter(ACC IN ("ListOfAccounts"))
But then I would have to create this Account Filter List recursively, but it would be less stress on the DB.
(Both Tables Ledger and Account are highly simplified in my example, they are very wide in real life!)
Kind regards,
Jörn. (or Joern for US keyboards!)
