cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aldredd
Advocate I
Advocate I

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

 

pbi.png

 

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
Vvelarde
Community Champion
Community Champion

hi @aldredd

 

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




Lima - Peru

View solution in original post

4 REPLIES 4
Vvelarde
Community Champion
Community Champion

hi @aldredd

 

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




Lima - Peru

View solution in original post

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

 

Works a treat, thank you

dtartaglia
Resolver I
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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.