Best Method for Grouping?

[musicbydannyd]

I am curious of your thoughts/opinions of the best method to go about this...

I am creating a financial statement summary from a SQL database of General Ledger transactions and need various combinations of companies (9), departments (80+) and account numbers (30,000+) assigned to a seperate line of the statment. What I'd like to do is create a lookup table with these various combinations (so I only have to do this once!) and obviously this can involve a great deal of detail. For example:

if Co-Dept-Acct# >=1222230000 and Co-Dept-Acct# <=1222239999 
or Co-Dept-Acct# >=1444430000 and Co-Dept-Acct# <=1444439999

or Co-Dept-Acct# =1555532100

(etc. through multiple Co-DeptAcct combinations)

then "Revenue Bucket 1"

(repeat for each revenue, asset, expense, etc. group)

These are the 3 methods I'm considering, but would love to hear of any other (simpler) options:

1. A custom column with multiple/nested if-then-else, AND & OR statements.
2. Creating a view on the SQL server using a 'CASE' statement.
3. The 'Group By' function in the query editor (which I've used before, but have little experience).

Thanks in advance!

[Anonymous]

@musicbydannyd This sounds like a good use case for dynamic segmentation. This concept and implementation is fully described by Marco Russo and Alberto Ferrari on their daxpatterns website here

If that doesn't directly apply, there are several other methods that you can use to build classifications. I would recommend the full list here under "Classification"

[musicbydannyd]

@Anonymous, thanks for the links! I've stolen used some of Marco Russo's methodology before and will definitely look into this. 🙂

Thanks again, DannyD