Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi, hope everyone is doing well. I'm new to Power BI and need community's help regarding a problem.
I've two tables which are unrelated as such. Table1 (IED) has a column (Attribute:name) that contains text such as B710_BCU, B710_21_1, etc. Table2 (IED_MP) has a column (TelegramPrefix) that contains several text with text of IED[Attribute:name] as prefix such as B710_BCUabc, B710_BCUefg, B710_21_1qrs, B710_21_1mnp, etc. I wish to create a new column IED_MP[IEDname] in Table2 with only the data of table1 column IED[Attribute:name] that is contained in Table2 column IED_MP[TelegramPrefix]. The text of each row of IED_MP[TelegramPrefix] is to be checked and matched with text of all rows of IED[Attribute:name]. If text of IED_MP[TelegramPrefix] begins with any text in IED[Attribute:name] then that text of IED[Attribute:name] is to be added in same row in new column IED_MP[IEDname]. It is to be automated, so DAX code is needed.
Table1:
IED[Attribute:name] |
B710_BCU |
B710_21_1 |
B711_BCU |
Table2:
IED_MP[TelegramPrefix] | IED_MP[IEDname]- Expected output |
B710_BCUAnUn_MeasTransdIn | B710_BCU |
B710_BCUApplication | B710_BCU |
B710_BCUVI3p1_5051OC3phA1 | B710_BCU |
B710_21_1PROT | B710_21_1 |
Excel sheet and pbix files are attached at following link for reference:
https://drive.google.com/drive/folders/1hpLf3B9rmT1fCk7fgoeA3KFrumkhKsZ8?usp=sharing
Thanks a lot in advance for much needed help.
Solved! Go to Solution.
Hello @SkewedCircle ,
Please try the below DAX codeby creating a calculated column, this should work..
IEDname = VAR PrefixValue = IED_MP[TelegramPrefix]
RETURN
MAXX(FILTER(Table1,LEFT(PrefixValue,
LEN(Table1[Attribute:name])) = Table1[Attribute:name]),Table1[Attribute:name])
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
Hello @SkewedCircle ,
Please try the below DAX codeby creating a calculated column, this should work..
IEDname = VAR PrefixValue = IED_MP[TelegramPrefix]
RETURN
MAXX(FILTER(Table1,LEFT(PrefixValue,
LEN(Table1[Attribute:name])) = Table1[Attribute:name]),Table1[Attribute:name])
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |