Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.