Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |