Forum Discussion
Solved
Substring and Approximative Match
Hi, I have an issue and I am really stuck here, let me explain it: Currently I have 2 sources and no exact matching. What do I want to achieve? I want to match SOURCE1.bucket/source w...
Hi,
This calculated column formula works
Column = FIRSTNONBLANK(FILTER(VALUES(Buckets[BUCKET]),SEARCH(Buckets[BUCKET],Data[FULL_DATABASE_NAME],1,0)),1)
What about using Text.Range with Text.Length from the Source column to get the bucket from the last column?
Post some more realistic data (not a picture) if you want more help, please.
Below what I could give as data, I hope it will be useful.
Thanks,
SOURCE 1
| FULL_DATABASE_NAME | BUCKET /SOURCE | WHAT I WANT (and I don't have) |
| landingconsumabledbprdaeranpill | aeranpill | aera |
| landingconsumabledbprdaerap6rs | aerap6rs | aera |
| landingconsumabledbprdaerap5op | aerap5op | aera |
| landingconsumabledbprdaerap3tr | aerap3tr | aera |
| landingconsumabledbprdaerap1te | aerap1te | aera |
| landingconsumabledbprdaeraplbou | aeraplbou | aera |
| landingconsumabledbprdaerasxyrl | aerasxyrl | aera |
| landingconsumabledbprdtelonip1ee | telonip1ee | teloni |
| landingconsumabledbprdtelanosp3ok | telanosp3ok | telanos |
| landingconsumabledbprdtelanosp1tr | telanosp1tr | telanos |
| landingconsumabledbprdtelanossmdl | telanossmdl | telanos |
SOURCE 2
| BUCKET |
| aera |
| telanos |
| teloni |
Kasiop Measure
Measure = MAXX ( FILTER ( CROSSJOIN ( tbl1, tbl2 ), CONTAINSSTRING ( tbl1[BUCKET /SOURCE], tbl2[BUCKET] ) = TRUE () ), [BUCKET] )Calculated Column
Column = VAR _0 = FILTER ( CROSSJOIN ( tbl1, tbl2 ), CONTAINSSTRING ( tbl1[BUCKET /SOURCE], tbl2[BUCKET] ) = TRUE () ) VAR _1 = MAXX ( FILTER ( _0, [FULL_DATABASE_NAME] = EARLIER ( tbl1[FULL_DATABASE_NAME] ) ), [BUCKET] ) RETURN _1Hi,
This calculated column formula works
Column = FIRSTNONBLANK(FILTER(VALUES(Buckets[BUCKET]),SEARCH(Buckets[BUCKET],Data[FULL_DATABASE_NAME],1,0)),1)thanks a lot Ashish, your solution works!!
Many thanks to all of you 😗!!!
You are welcome.
