Create a Calculated Column from Different Tables using if formulae from Azure SQL Database

[JasonG-BI]

Hi All,

I have a direct query link to a data base which has multiple tables.

Basic

I am trying to create an column that basical says, if the Column 1 which is in table 1 = something, then subtract Column 2

from Column 3 in table 2.  The reason for not just using filters is that i want to create further calculations using the newly created columns.

Detail

 One of my tables is a list of a Nominal Codes called "Accounts Nominal_Ledger_Code"  and the other table is a table of actual transactions"Accounts Nominal_Ledger_Transaction_Posting" the are linked via nominal code.  So i am trying to say if the nominal code =7001 in "Accounts Nominal_Ledger_Code" then Subtract Credit Value from Debit Value .

I have tried using the if formulae but i am relatively new to BI and i am stuck on this one, any help is much appreciated. I but a copy of the tables and columns in question above.

[Anonymous · Accepted solution]

Be careful using calculated columns...the dramatically increase the size of your data model.  There's nothing inherently wrong with calculated columns, and it's a great way to create a column to use as an attribute (think slicer or something you want on the x-axis of a column chart).

I'm guessing you have a 1:* relationship between Code table and Transaction Posting table.

This code will create a calculated column in your Transaction Posting table with the value you seek:

Column =
IF (
    RELATED ( 'Accounts Nominal_Ledger_Code'[Nominal_Code] ) = 7001,
    /*This assumes that the [Nominal_Code] column is formatted as a number, use "7001" if it's formatted as text*/
    'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value]
        - 'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value],
    BLANK ()
)

This is the same logic but set up as a measure.  This will take all of the rows of the Transaction Posting table in the current filter context, and then apply the additional filter of Nominal Code = 7001.  It will perform the subraction that you described, and then add up each of those individual results.

[Measure] =
CALCULATE (
    SUMX (
        'Accounts Nominal_Ledger_Transaction_Posting',
        'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value]
            - 'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value]
    ),
    'Accounts Nominal_Ledger_Code'[Nominal_Code] = 7001
)

A final piece of advice.  Come up with user friendly names for your tables.  I recommend "LedgerCode" and "Transactions"  It will make your code much easier to follow.

[Measure] =
CALCULATE (
    SUMX (
        Transactions,
        Transactions[Debit_Value]
            - Transactions[Credit_Value]
    ),
    LedgerCode[Nominal_Code] = 7001
)

[Anonymous · Accepted solution]

Column with OR =
IF (
    RELATED ( 'Accounts Nominal_Ledger_Code'[Nominal_Code] ) 
        IN {"Description 1", "Description 2", "Description 3", etc.},
    /*Add as many descriptions as you want inside quotes and separated by commas.  Note the CURLY BRACES to define this as a list.*/
    'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value]
        - 'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value],
    BLANK ()
)

[Anonymous · Accepted solution]

Be careful using calculated columns...the dramatically increase the size of your data model.  There's nothing inherently wrong with calculated columns, and it's a great way to create a column to use as an attribute (think slicer or something you want on the x-axis of a column chart).

I'm guessing you have a 1:* relationship between Code table and Transaction Posting table.

This code will create a calculated column in your Transaction Posting table with the value you seek:

Column =
IF (
    RELATED ( 'Accounts Nominal_Ledger_Code'[Nominal_Code] ) = 7001,
    /*This assumes that the [Nominal_Code] column is formatted as a number, use "7001" if it's formatted as text*/
    'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value]
        - 'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value],
    BLANK ()
)

This is the same logic but set up as a measure.  This will take all of the rows of the Transaction Posting table in the current filter context, and then apply the additional filter of Nominal Code = 7001.  It will perform the subraction that you described, and then add up each of those individual results.

[Measure] =
CALCULATE (
    SUMX (
        'Accounts Nominal_Ledger_Transaction_Posting',
        'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value]
            - 'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value]
    ),
    'Accounts Nominal_Ledger_Code'[Nominal_Code] = 7001
)

A final piece of advice.  Come up with user friendly names for your tables.  I recommend "LedgerCode" and "Transactions"  It will make your code much easier to follow.

[Measure] =
CALCULATE (
    SUMX (
        Transactions,
        Transactions[Debit_Value]
            - Transactions[Credit_Value]
    ),
    LedgerCode[Nominal_Code] = 7001
)

[JasonG-BI]

Thanks For your help on this i ended up using th column. Is there a way in can use the above but for multiple options? i.e If OR function so if it equals this or, or, or this add the the two columns is there a simpler way than the below.

Parts Workshop COS-1 =
IF (
    RELATED ( 'Accounts Nominal_Ledger_Code'[Description] ) = "Parts Cost Service - Retail",
    'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value]-'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value],
    BLANK ())+IF (
    RELATED ( 'Accounts Nominal_Ledger_Code'[Description] ) = "Parts Cost Service Non Franchise",
    'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value]-'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value],
    BLANK ())

[Anonymous · Accepted solution]

Column with OR =
IF (
    RELATED ( 'Accounts Nominal_Ledger_Code'[Nominal_Code] ) 
        IN {"Description 1", "Description 2", "Description 3", etc.},
    /*Add as many descriptions as you want inside quotes and separated by commas.  Note the CURLY BRACES to define this as a list.*/
    'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value]
        - 'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value],
    BLANK ()
)

[JasonG-BI]

Thats Great Thanks Chris, I Take your point about using measures is it possible to use the above forumlae as a measure? with multiple or filters? 

[Anonymous · Accepted solution]

[Measure] =
CALCULATE (
    SUMX (
        'Accounts Nominal_Ledger_Transaction_Posting',
        'Accounts Nominal_Ledger_Transaction_Posting'[Debit_Value]
            - 'Accounts Nominal_Ledger_Transaction_Posting'[Credit_Value]
    ),
    'Accounts Nominal_Ledger_Code'[Nominal_Code] IN  {"Code 1", "Code 2", "Code 3"...}
)

Same structure as the first measure I gave you.  I just used the

IN {"Code 1", Code 2", "Code 3"...} instead of = Code 1 approach.  IN works as a multiple OR statement.