Custom column to switch GL Code to GL Category Name

DAX DAX Calculations
1

I am trying to build a P&L Report but my data table does not contain a column that identifies the entries in each P&L Category. How can create a column that converts the GL Code to it’s appropriate GL Category NameFINANCE DATASET SMALL VERSION.pbix (3.2 MB) :

  1. Current Assets = 1000-1999
  2. Long Term Assets = 2000-3999
  3. Current Liabilities = 4000-4999
  4. Non Current Liabilities = 5000-6999
  5. Equity = 7000-7999
  6. Revenue = 8000-8999
  7. Cost of Sales = 9000-9099
  8. Expenses = 9100-9999
2

@jmolina,

The ever-versatile SWITCH( TRUE() ) construct works great here. Just be sure first to change your Account Code field from text to whole number in Power Query.

Here’s the DAX for a calculated column to do the translation from code to category name:

    GL Category = 

SWITCH( TRUE(),
    'GL Accounts'[Account Code] < 1000, "Undefined",
    'GL Accounts'[Account Code] < 2000, "Current Assets",
    'GL Accounts'[Account Code] < 4000, "Long Term Assets",
    'GL Accounts'[Account Code] < 5000, "Current Liabilities",
    'GL Accounts'[Account Code] < 7000, "Non Current Liabilities",
    'GL Accounts'[Account Code] < 8000, "Equity",
    'GL Accounts'[Account Code] < 9000, "Revenue",
    'GL Accounts'[Account Code] < 9100, "Cost of Sales",
    'GL Accounts'[Account Code] < 10000, "Expenses",
    "ERROR"
)

Hope this is helpful.

  • Brian
3

Hi @jmolina, we’ve noticed that no response has been received from you within the last 24 hrs. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!