Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to Solution.
hi @aldredd
AbsoluteExpenditure = If(RELATED(TransactionDescriptions[ParentReasons])<>"Income",Abs(BankTransactions[Value]),0)
hi @aldredd
AbsoluteExpenditure = If(RELATED(TransactionDescriptions[ParentReasons])<>"Income",Abs(BankTransactions[Value]),0)
Thank you @Vvelarde - I could swear I tried that and didn't work - must have made an error!
Works a treat, thank you
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |