cancel
Showing results for
Did you mean:

Calculations depending on value in column in related table

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)

1 ACCEPTED SOLUTION
Community Champion

hi @aldredd

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

Lima - Peru
4 REPLIES 4
Community Champion

hi @aldredd

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

Lima - Peru

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

Works a treat, thank you

Resolver I

Will it work with VALUES:

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

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

Announcements

Launching new user group features

Learn how to create your own user groups today!