Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
Currently, my data table has 'main' contracts (e.g. 328487) and also 'supplement' contracts (e.g. 328488), which can have many rows and are linked together in Column LinkRef1. I would like to create a new column to lookup MaturityDate of the supplement contracts to check if it is the same as the main contract:
This seems to give an error message 'A table of multiple values was supplied where a single value was expected'.
Ultimately, I would like to create this check for ValueDate, MaturityDate, and PrincipalFace. What would be the best solution for this?
Any help would be highly appreciated. Thank you!
Sample data:
ContNo | LegNumber | LegType | LinkRef1 | TradeDate | ValueDate | MaturityDate | Original_Curr | PrincipalFace |
328488 | 0 | Pay | 328487 | Tue, 29/Aug/23 | Thu, 31/Aug/23 | Thu, 28/Nov/24 | EUR | 10000000 |
328488 | 0 | Receive | 328487 | Tue, 29/Aug/23 | Thu, 31/Aug/23 | Thu, 28/Nov/24 | EUR | -10000000 |
328487 | 0 | Invest | 328488 | Tue, 29/Aug/23 | Thu, 31/Aug/23 | Thu, 28/Nov/24 | EUR | -10000000 |
Solved! Go to Solution.
maybe you can try this
Column = if(ISBLANK(maxx(FILTER('Table','Table'[ContNo]=EARLIER('Table'[LinkRef1])),'Table'[ContNo])),"No","Yes")
Proud to be a Super User!
Hi @pkruarat
Seems like you're trying to lookup a value from a column that has multiple values (ideally there should be one).
See if this formula helps you in any way:
Example =
CALCULATE (
FIRSTNONBLANK (Table[MaturityDate], 1 ),
FILTER ( ALL (Table), Table[ContNo] = Table2[LinkRef1])
)
Hello @azakir ,
Thank you for your suggestion! But, this seems to return just the lowest value/earliest date in the MaturityDate column for all rows.
what's the expected output based on the sample data you provided?
Proud to be a Super User!
Hello @ryan_mayu ,
The expected output is Yes for all 3 lines, please, because MaturityDate of 328488 and 328487 are the same.
I understand that the problem is that there are 2 lines for 328488. Is there a way to check against the first available matched value? They are matched through Column LinkRef1.
ContNo | LegNumber | LegType | LinkRef1 | TradeDate | ValueDate | MaturityDate | Original_Curr | PrincipalFace | MDCheck |
328488 | 0 | Pay | 328487 | Tue, 29/Aug/23 | Thu, 31/Aug/23 | Thu, 28/Nov/24 | EUR | 10000000 | Yes |
328488 | 0 | Receive | 328487 | Tue, 29/Aug/23 | Thu, 31/Aug/23 | Thu, 28/Nov/24 | EUR | -10000000 | Yes |
328487 | 0 | Invest | 328488 | Tue, 29/Aug/23 | Thu, 31/Aug/23 | Thu, 28/Nov/24 | EUR | -10000000 | Yes |
maybe you can try this
Column = if(ISBLANK(maxx(FILTER('Table','Table'[ContNo]=EARLIER('Table'[LinkRef1])),'Table'[ContNo])),"No","Yes")
Proud to be a Super User!
This solved my problem! Thank you so much!
I'll use this to check other values as well 😃
you are welcome
Proud to be a Super User!
User | Count |
---|---|
84 | |
69 | |
69 | |
65 | |
54 |
User | Count |
---|---|
93 | |
93 | |
91 | |
76 | |
69 |