Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 )
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 () )
[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.
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 )
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 ())
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 () )
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?
[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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |