DAX formula that finds specific and separate words in a text string
I have a table which contains text ('TextString'[Text]) and I need a DAX formula in a calculated column which can find specific words taken from another table 'Matchlist'[Keyword].
However, the formula must ignore instances when the keyword is found in the string as part of another word. For instance, the text string "Modification Cost" contains the keyword "cat" but it should be ignored because it is a part of another word "modification" and is not a separate word.
Can you please help me to modify the below formula, so it would take into account this additional requirement?
=IF(
SUMX('Matchlist',
FIND(
UPPER('Matchlist'[Keyword]),
UPPER('TextString'[Text])
,,0
)
) > 0,
"YES",
"No"
)
I am using Power Pivot and Excel 365. Thanks!
dax powerpivot
asked Nov 24 '18 at 21:04
RomanRoman
113
add a comment |
I have a table which contains text ('TextString'[Text]) and I need a DAX formula in a calculated column which can find specific words taken from another table 'Matchlist'[Keyword].
However, the formula must ignore instances when the keyword is found in the string as part of another word. For instance, the text string "Modification Cost" contains the keyword "cat" but it should be ignored because it is a part of another word "modification" and is not a separate word.
Can you please help me to modify the below formula, so it would take into account this additional requirement?
=IF(
SUMX('Matchlist',
FIND(
UPPER('Matchlist'[Keyword]),
UPPER('TextString'[Text])
,,0
)
) > 0,
"YES",
"No"
)
I am using Power Pivot and Excel 365. Thanks!
dax powerpivot
asked Nov 24 '18 at 21:04
RomanRoman
113
add a comment |
I have a table which contains text ('TextString'[Text]) and I need a DAX formula in a calculated column which can find specific words taken from another table 'Matchlist'[Keyword].
However, the formula must ignore instances when the keyword is found in the string as part of another word. For instance, the text string "Modification Cost" contains the keyword "cat" but it should be ignored because it is a part of another word "modification" and is not a separate word.
Can you please help me to modify the below formula, so it would take into account this additional requirement?
=IF(
SUMX('Matchlist',
FIND(
UPPER('Matchlist'[Keyword]),
UPPER('TextString'[Text])
,,0
)
) > 0,
"YES",
"No"
)
I am using Power Pivot and Excel 365. Thanks!
dax powerpivot
asked Nov 24 '18 at 21:04
RomanRoman
113
I have a table which contains text ('TextString'[Text]) and I need a DAX formula in a calculated column which can find specific words taken from another table 'Matchlist'[Keyword].
However, the formula must ignore instances when the keyword is found in the string as part of another word. For instance, the text string "Modification Cost" contains the keyword "cat" but it should be ignored because it is a part of another word "modification" and is not a separate word.
Can you please help me to modify the below formula, so it would take into account this additional requirement?
=IF(
SUMX('Matchlist',
FIND(
UPPER('Matchlist'[Keyword]),
UPPER('TextString'[Text])
,,0
)
) > 0,
"YES",
"No"
)
I am using Power Pivot and Excel 365. Thanks!
dax powerpivot
dax powerpivot
asked Nov 24 '18 at 21:04
RomanRoman
113
asked Nov 24 '18 at 21:04
RomanRoman
113
asked Nov 24 '18 at 21:04
RomanRoman
113
asked Nov 24 '18 at 21:04
RomanRoman
113
asked Nov 24 '18 at 21:04
RomanRoman
113
113
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
One way might be to split the original text into separate columns using Edit queries in data view, and Split Column by delimiter (which could be ' ').
You could then use COUNTROWS something along the lines of
=CALCULATE (
COUNTROWS ( 'MatchList' ),
UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path1] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path2] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path3] )
etc...
)
answered Nov 25 '18 at 19:42
James HamiltonJames Hamilton
513
Including a link is fine, but you should include an explanation and supporting code for your answer.
– anothermh
Nov 25 '18 at 19:56
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462344%2fdax-formula-that-finds-specific-and-separate-words-in-a-text-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
One way might be to split the original text into separate columns using Edit queries in data view, and Split Column by delimiter (which could be ' ').
You could then use COUNTROWS something along the lines of
=CALCULATE (
COUNTROWS ( 'MatchList' ),
UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path1] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path2] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path3] )
etc...
)
answered Nov 25 '18 at 19:42
James HamiltonJames Hamilton
513
Including a link is fine, but you should include an explanation and supporting code for your answer.
– anothermh
Nov 25 '18 at 19:56
add a comment |
One way might be to split the original text into separate columns using Edit queries in data view, and Split Column by delimiter (which could be ' ').
You could then use COUNTROWS something along the lines of
=CALCULATE (
COUNTROWS ( 'MatchList' ),
UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path1] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path2] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path3] )
etc...
)
answered Nov 25 '18 at 19:42
James HamiltonJames Hamilton
513
Including a link is fine, but you should include an explanation and supporting code for your answer.
– anothermh
Nov 25 '18 at 19:56
add a comment |
One way might be to split the original text into separate columns using Edit queries in data view, and Split Column by delimiter (which could be ' ').
You could then use COUNTROWS something along the lines of
=CALCULATE (
COUNTROWS ( 'MatchList' ),
UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path1] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path2] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path3] )
etc...
)
answered Nov 25 '18 at 19:42
James HamiltonJames Hamilton
513
One way might be to split the original text into separate columns using Edit queries in data view, and Split Column by delimiter (which could be ' ').
You could then use COUNTROWS something along the lines of
=CALCULATE (
COUNTROWS ( 'MatchList' ),
UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path1] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path2] )
|| UPPER ( 'TextString'[Text] ) = UPPER ( 'MatchList'[Path3] )
etc...
)
answered Nov 25 '18 at 19:42
James HamiltonJames Hamilton
513
edited Nov 25 '18 at 20:04
answered Nov 25 '18 at 19:42
James HamiltonJames Hamilton
513
answered Nov 25 '18 at 19:42
James HamiltonJames Hamilton
513
answered Nov 25 '18 at 19:42
James HamiltonJames Hamilton
513
513
Including a link is fine, but you should include an explanation and supporting code for your answer.
– anothermh
Nov 25 '18 at 19:56
add a comment |
Including a link is fine, but you should include an explanation and supporting code for your answer.
– anothermh
Nov 25 '18 at 19:56
Including a link is fine, but you should include an explanation and supporting code for your answer.
– anothermh
Nov 25 '18 at 19:56
Including a link is fine, but you should include an explanation and supporting code for your answer.
– anothermh
Nov 25 '18 at 19:56
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462344%2fdax-formula-that-finds-specific-and-separate-words-in-a-text-string%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
