Possible to loop through cell string?

[msp1982dk]

Hi there,

I have two tables in my DataModel.

GL Account
This table describes the GL accounts. Some of the accounts are totaling accounts, ie they sum the total of other accounts. Totaling accounts have a text string that describes which accounts they should include in their total, eg:

• "X" <- Includes only account X.
• "X..Y" <- Includes accounts in range X to Y.
• "X..Y|Z" <- Includes accounts in range X to Y plus account Z.

GL Entry
This table holds the GL postings. Each entry has a value and an account.

I would like to be able to select a totaling account and then get a grand total of all entries related to that account either directly or indirectly (through totaling accounts). But how do I best accomplish this? Does DAX have a foreach function of sorts that can run through the text string? Or should I somehow create a new table for account relationships?

Hope you can help!

Thanks,
Martin

 

[scottsen]

Yikes. I dunno what your max complicated crazy string is, but... I would think about parsing and simplifying in Power Query.

 

[ImkeF]

I strongly recommend to use an account groups table for this request:

First column contains the numbers of all your accounts (real accounts only). Second contains the name of your account. Third column will carry the name of the subtotals that each account belongs to (in your initial general ledger setup). Fourth column carries the sort order of the entries in column 4, and will be used as the “sort by” column for col3, as otherwise it would be sorted alphabetically.

Fifth and following columns can contain the other allocations that you’ve dreamt of having at your fingertips / are defined in your accounts schedules (if you’re using Dynamics NAV ie)….

You will basically be able to create all sorts of different P&L and Balance reports with different subtotals and orderings, simply by dragging your desired account scheme col first and then add the details col (number and/or name).

If this is still unclear, wait for my blogpost about it which I will hopefully be able to publish soon (will post the link once it is available)