Calculations depending on value in column in related table

[aldredd]

Hi guys, after a little guidance

two tables - one contains Transactions, the other Transaction Reasons (ParentReason and ChildReason)

Tables are matched on TransactionID on a 1-1 basis

I'm trying to create a new field in BankTransactions which creates an absolute of Value, but only if the ParentReason is not 'Income'

I've create the below formula...

AbsoluteExpenditure = IF(TransactionDescriptions[ParentReason] <> "Income", ABS(BankTransactions[Value]),0)

But get the error

A single value for column 'ParentReason' in table 'TransactionDescriptions' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Given it's a 1-1 relationship, I suspect my fault is elsewhere. I've seen references to use RELATEDTABLE() but can't quite see how I should be using it (if at all)

[Vvelarde · Accepted solution]

hi @aldredd

AbsoluteExpenditure = If(RELATED(TransactionDescriptions[ParentReasons])<>"Income",Abs(BankTransactions[Value]),0)

[aldredd]

Thank you @Vvelarde - I could swear I tried that and didn't work - must have made an error!

Works a treat, thank you

[dtartaglia]

Will it work with VALUES:

IF(VALUES(TransactionDescriptions[ParentReason])...

[aldredd]

Thanks, gave that a go but tells me I can't use VALUES with a directquery

Is that the issue - that I'm using Directquery?

I can do what I need to do within the source data, but keen to figure out if its possible within BPI