topic Re: Dax formula to do a lookup/related in another table in DAX Commands and Tips

Dax formula to do a lookup/related in another table

michellepace — Mon, 10 Aug 2020 13:44:14 GMT

Hello,

I am at the beginning of my Dax learning curve and really struggling. I'm importing accounting transactional data where income is postive and expenses are negative. So far I have this formula which works fine:

Actuals = SUM( 'Account Transactions'[Amount] )

Now, I'd like to represent my negtaive values as postive numbers in my matrix as well as graphs. If I write it in psudeo code, this is what it looks like:

Actuals_ForMatrix&Graphs = var isExpense = if([IncomeExpense]="Expense", true, false) **need help here! return if (isExpense, [Actuals] * -1, [Actuals])

Can anyone please help me write the dax I need?

Re: Dax formula to do a lookup/related in another table

Greg_Deckler — Mon, 10 Aug 2020 13:50:43 GMT

michellepace - You should just be able to wrap your calculation with ABS function

Re: Dax formula to do a lookup/related in another table

Fowmy — Mon, 10 Aug 2020 13:56:51 GMT

michellepace

Try

= ABS([Actuals])

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Re: Dax formula to do a lookup/related in another table

michellepace — Mon, 10 Aug 2020 14:06:10 GMT

Hi Fowmy, thank you for your reply. I'm afraid it is not as simple as wrapping with an ABS function. For my expenses, I need to change positive values to negative values and negative values to positive values. To wrap it with an ABS function would throw my numbers out. For example, you can incur an expense when buying 100 boxes but then ask your supplier for a credit note for 20 of thos boxes. I really need to do as I say, multiple by (-1) for every ledger transactional line where [IncomeExpense]="Expense".

Also, I'm really keen to wrap my head around this little piece of dax, I'm hoping it will help in my undrestanding. Surely it cannot be that difficult? It's pretty simple in exel 🙂

Re: Dax formula to do a lookup/related in another table

amitchandak — Mon, 10 Aug 2020 15:50:30 GMT

michellepace , Try like ?

if( [IncomeExpense]="Expense",-1*[Actuals] ,[Actuals] )

Re: Dax formula to do a lookup/related in another table

michellepace — Mon, 10 Aug 2020 16:19:40 GMT

Hi amitchandak , please take a look at my original question. [IncomeExpense] is not a measure I have defined. That is the problem. I don't know how to look up a value. Please read my question and let me know your thoughts?

Re: Dax formula to do a lookup/related in another table

Fowmy — Mon, 10 Aug 2020 16:57:41 GMT

michellepace

Try this measure:

You mentioned + into - and the other way is required in the comment but your measure tries to do the following leaving Income as it is.

Actuals_ForMatrix&Graphs = SUMX( 'Account Transactions', IF( RELATED('Account Categories'[IncomeExpense]) = "Expense", [Actuals] * -1, [Actuals] ) )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Re: Dax formula to do a lookup/related in another table

michellepace — Mon, 10 Aug 2020 17:17:49 GMT

Thank you Fowmy !! It's so simple when you know how :). Thanks very much, exactly what I was looking for.

I figured out another way to achieve the same thing using PowerQuery editor. I merged the IncomeExpense column from table AccountCategories into the table AccountTransactions. Then I created a conditional column using:

= Table.AddColumn(#"Expanded Account Categories", "Amount(+)", each if [Account Categories.IncomeExpense] = "Expense" then [Amount]*-1 else [Amount])

My Last Question:
In terms of performance, is there a preferable way and which is best? Using dax as you've shown, or doing it with Power Query (for example, is one better for performance than another)?

Re: Dax formula to do a lookup/related in another table

Fowmy — Mon, 10 Aug 2020 17:24:46 GMT

michellepace

Glad it worked for you!

It is better to keep it with DAX it is more on the calculation side. When you do in PQ, it adds additional data load and it is static.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn