Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
i tried LOOKUPVALUE - SEARCH, tried DAX and power query but no success.
searched the forum but cant find the awnser as well. so here it goes!
i got 2 tables
- CallCategory
- report
my question is as follow
if CallCategory[keyword] excists in report[text] than insert CallCategory[category] in report[category]
like a conditional column only the input of the values is a table
thanks in advance!
Solved! Go to Solution.
Hi, @EUR_MSC
You can try the following methods.
Measure:
Measure = RIGHT(LEFT(SELECTEDVALUE(Report[text]),FIND("keyword",SELECTEDVALUE(Report[text]),1,BLANK())+7),8)
category 2 = CALCULATE(MAX(CallCategory[category]),FILTER(ALL(CallCategory),[keyword]=[Measure]))
Find function: Returns the starting position of one text string within another text string.
Then use Right and Left to extract the string, which can be compared with the keyword column of the CallCategory table.
FIND function (DAX) - DAX | Microsoft Learn
RIGHT function (DAX) - DAX | Microsoft Learn
LEFT function (DAX) - DAX | Microsoft Learn
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @EUR_MSC
You can try the following methods.
Measure:
Measure = RIGHT(LEFT(SELECTEDVALUE(Report[text]),FIND("keyword",SELECTEDVALUE(Report[text]),1,BLANK())+7),8)
category 2 = CALCULATE(MAX(CallCategory[category]),FILTER(ALL(CallCategory),[keyword]=[Measure]))
Find function: Returns the starting position of one text string within another text string.
Then use Right and Left to extract the string, which can be compared with the keyword column of the CallCategory table.
FIND function (DAX) - DAX | Microsoft Learn
RIGHT function (DAX) - DAX | Microsoft Learn
LEFT function (DAX) - DAX | Microsoft Learn
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
almost there!
it works if your keywords are always the same number of characters.
i got keywords like
audio
headset
microfoon
controller
etc.
Measure = RIGHT(LEFT(SELECTEDVALUE(Report[text]),FIND("keyword",SELECTEDVALUE(Report[text]),1,BLANK())+7),8)
if i edit the 7 to 2 it shows the keyword "pc" if i change the 7 to 5 it shows the keywords "audio" & "video"
Hello,
Try this:
looks like you cant use this over 2 tables? i can only select values from the table report or measures
If you can have a relationship between the two tables, then call the columns that you want to evaluate from the other tables using lookup value.
I think this can work only if there is a validated relationship between the two tables
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |