Power BI DAX - Count number of records if contains string from a reference table
How do I write a DAX function to count the number of cells in a column contain a string, from a list of strings in a reference table.
For example, imagine that I have a items table and a reference table:
Items Table:
id items_list
1 hats, jar, shoes
2 Fedora, key, Pens
3 Fedora, jars, Pens
4 baseball cap, Sandals
Reference Type Table:
id hat_types shoe_types
1 hat shoes
2 fedora sandals
3 cap
I would like to write a DAX function that takes in the strings from the Reference Type table and checks that if the string is contained in the Items table. And if it does, then count the number of rows.
For example:
When looking to see if the strings in the hat_types column are contained in the items_list, I can count 4 rows.
When looking to see if the strings in the shoe_types column are contained in the items_list, I can count 2 rows (first and last row).
How can I do this on Power BI?
Any help you can give is much appreciated!
Thank you
powerbi dax
asked Nov 11 at 23:16
bowernewb
111
add a comment |
How do I write a DAX function to count the number of cells in a column contain a string, from a list of strings in a reference table.
For example, imagine that I have a items table and a reference table:
Items Table:
id items_list
1 hats, jar, shoes
2 Fedora, key, Pens
3 Fedora, jars, Pens
4 baseball cap, Sandals
Reference Type Table:
id hat_types shoe_types
1 hat shoes
2 fedora sandals
3 cap
I would like to write a DAX function that takes in the strings from the Reference Type table and checks that if the string is contained in the Items table. And if it does, then count the number of rows.
For example:
When looking to see if the strings in the hat_types column are contained in the items_list, I can count 4 rows.
When looking to see if the strings in the shoe_types column are contained in the items_list, I can count 2 rows (first and last row).
How can I do this on Power BI?
Any help you can give is much appreciated!
Thank you
powerbi dax
asked Nov 11 at 23:16
bowernewb
111
Welcome to Stack Overflow! Please edit to add meaningful code and a problem description here. Posting a Minimal, Complete, and Verifiable example that demonstrates your problem will increase your chances of getting good answers.
– lucascaro
Nov 11 at 23:40
add a comment |
How do I write a DAX function to count the number of cells in a column contain a string, from a list of strings in a reference table.
For example, imagine that I have a items table and a reference table:
Items Table:
id items_list
1 hats, jar, shoes
2 Fedora, key, Pens
3 Fedora, jars, Pens
4 baseball cap, Sandals
Reference Type Table:
id hat_types shoe_types
1 hat shoes
2 fedora sandals
3 cap
I would like to write a DAX function that takes in the strings from the Reference Type table and checks that if the string is contained in the Items table. And if it does, then count the number of rows.
For example:
When looking to see if the strings in the hat_types column are contained in the items_list, I can count 4 rows.
When looking to see if the strings in the shoe_types column are contained in the items_list, I can count 2 rows (first and last row).
How can I do this on Power BI?
Any help you can give is much appreciated!
Thank you
powerbi dax
asked Nov 11 at 23:16
bowernewb
111
How do I write a DAX function to count the number of cells in a column contain a string, from a list of strings in a reference table.
For example, imagine that I have a items table and a reference table:
Items Table:
id items_list
1 hats, jar, shoes
2 Fedora, key, Pens
3 Fedora, jars, Pens
4 baseball cap, Sandals
Reference Type Table:
id hat_types shoe_types
1 hat shoes
2 fedora sandals
3 cap
I would like to write a DAX function that takes in the strings from the Reference Type table and checks that if the string is contained in the Items table. And if it does, then count the number of rows.
For example:
When looking to see if the strings in the hat_types column are contained in the items_list, I can count 4 rows.
When looking to see if the strings in the shoe_types column are contained in the items_list, I can count 2 rows (first and last row).
How can I do this on Power BI?
Any help you can give is much appreciated!
Thank you
powerbi dax
powerbi dax
asked Nov 11 at 23:16
bowernewb
111
asked Nov 11 at 23:16
bowernewb
111
asked Nov 11 at 23:16
bowernewb
111
asked Nov 11 at 23:16
bowernewb
111
asked Nov 11 at 23:16
bowernewb
111
111
Welcome to Stack Overflow! Please edit to add meaningful code and a problem description here. Posting a Minimal, Complete, and Verifiable example that demonstrates your problem will increase your chances of getting good answers.
– lucascaro
Nov 11 at 23:40
add a comment |
Welcome to Stack Overflow! Please edit to add meaningful code and a problem description here. Posting a Minimal, Complete, and Verifiable example that demonstrates your problem will increase your chances of getting good answers.
– lucascaro
Nov 11 at 23:40
Welcome to Stack Overflow! Please edit to add meaningful code and a problem description here. Posting a Minimal, Complete, and Verifiable example that demonstrates your problem will increase your chances of getting good answers.
– lucascaro
Nov 11 at 23:40
Welcome to Stack Overflow! Please edit to add meaningful code and a problem description here. Posting a Minimal, Complete, and Verifiable example that demonstrates your problem will increase your chances of getting good answers.
– lucascaro
Nov 11 at 23:40
add a comment |
1 Answer
1
active
oldest
votes
You can do something like this:
ShoeCount =
SUMX(Items,
IF(
SUMX(ReferenceType,
IF(
LEN(SUBSTITUTE(LOWER(Items[items_list]), ReferenceType[shoe_types], ""))
< LEN(Items[items_list]),
1
)
) > 0,
1
)
)
This iterates through each row in the Items table and checks if any of the shoe_types are a substring of items_list. If so, then you add one to the count.
I test for substring by attempting to substitute any occurrence of the shoe_types string with the empty string "" and checking if that made the whole items_list string shorter.
Note: LOWERconverts the string to all lower case for better matching.
The hat counting measure can be done analogously. All you need to do is change shoe_types to hat_types.
answered Nov 12 at 16:21
Alexis Olson
12.4k21633
Thank you Alexis for your answer. I believe the substitute function looks at the whole cell, while I need to check if the the cell contains a value from the reference table.
– bowernewb
Nov 13 at 22:01
It checks if it's contained by comparing the length of the string with and without substitution.
– Alexis Olson
Nov 13 at 22:09
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%2f53254212%2fpower-bi-dax-count-number-of-records-if-contains-string-from-a-reference-table%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
You can do something like this:
ShoeCount =
SUMX(Items,
IF(
SUMX(ReferenceType,
IF(
LEN(SUBSTITUTE(LOWER(Items[items_list]), ReferenceType[shoe_types], ""))
< LEN(Items[items_list]),
1
)
) > 0,
1
)
)
This iterates through each row in the Items table and checks if any of the shoe_types are a substring of items_list. If so, then you add one to the count.
I test for substring by attempting to substitute any occurrence of the shoe_types string with the empty string "" and checking if that made the whole items_list string shorter.
Note: LOWERconverts the string to all lower case for better matching.
The hat counting measure can be done analogously. All you need to do is change shoe_types to hat_types.
answered Nov 12 at 16:21
Alexis Olson
12.4k21633
Thank you Alexis for your answer. I believe the substitute function looks at the whole cell, while I need to check if the the cell contains a value from the reference table.
– bowernewb
Nov 13 at 22:01
It checks if it's contained by comparing the length of the string with and without substitution.
– Alexis Olson
Nov 13 at 22:09
add a comment |
You can do something like this:
ShoeCount =
SUMX(Items,
IF(
SUMX(ReferenceType,
IF(
LEN(SUBSTITUTE(LOWER(Items[items_list]), ReferenceType[shoe_types], ""))
< LEN(Items[items_list]),
1
)
) > 0,
1
)
)
This iterates through each row in the Items table and checks if any of the shoe_types are a substring of items_list. If so, then you add one to the count.
I test for substring by attempting to substitute any occurrence of the shoe_types string with the empty string "" and checking if that made the whole items_list string shorter.
Note: LOWERconverts the string to all lower case for better matching.
The hat counting measure can be done analogously. All you need to do is change shoe_types to hat_types.
answered Nov 12 at 16:21
Alexis Olson
12.4k21633
Thank you Alexis for your answer. I believe the substitute function looks at the whole cell, while I need to check if the the cell contains a value from the reference table.
– bowernewb
Nov 13 at 22:01
It checks if it's contained by comparing the length of the string with and without substitution.
– Alexis Olson
Nov 13 at 22:09
add a comment |
You can do something like this:
ShoeCount =
SUMX(Items,
IF(
SUMX(ReferenceType,
IF(
LEN(SUBSTITUTE(LOWER(Items[items_list]), ReferenceType[shoe_types], ""))
< LEN(Items[items_list]),
1
)
) > 0,
1
)
)
This iterates through each row in the Items table and checks if any of the shoe_types are a substring of items_list. If so, then you add one to the count.
I test for substring by attempting to substitute any occurrence of the shoe_types string with the empty string "" and checking if that made the whole items_list string shorter.
Note: LOWERconverts the string to all lower case for better matching.
The hat counting measure can be done analogously. All you need to do is change shoe_types to hat_types.
answered Nov 12 at 16:21
Alexis Olson
12.4k21633
You can do something like this:
ShoeCount =
SUMX(Items,
IF(
SUMX(ReferenceType,
IF(
LEN(SUBSTITUTE(LOWER(Items[items_list]), ReferenceType[shoe_types], ""))
< LEN(Items[items_list]),
1
)
) > 0,
1
)
)
This iterates through each row in the Items table and checks if any of the shoe_types are a substring of items_list. If so, then you add one to the count.
I test for substring by attempting to substitute any occurrence of the shoe_types string with the empty string "" and checking if that made the whole items_list string shorter.
Note: LOWERconverts the string to all lower case for better matching.
The hat counting measure can be done analogously. All you need to do is change shoe_types to hat_types.
answered Nov 12 at 16:21
Alexis Olson
12.4k21633
answered Nov 12 at 16:21
Alexis Olson
12.4k21633
answered Nov 12 at 16:21
Alexis Olson
12.4k21633
answered Nov 12 at 16:21
Alexis Olson
12.4k21633
12.4k21633
Thank you Alexis for your answer. I believe the substitute function looks at the whole cell, while I need to check if the the cell contains a value from the reference table.
– bowernewb
Nov 13 at 22:01
It checks if it's contained by comparing the length of the string with and without substitution.
– Alexis Olson
Nov 13 at 22:09
add a comment |
Thank you Alexis for your answer. I believe the substitute function looks at the whole cell, while I need to check if the the cell contains a value from the reference table.
– bowernewb
Nov 13 at 22:01
It checks if it's contained by comparing the length of the string with and without substitution.
– Alexis Olson
Nov 13 at 22:09
Thank you Alexis for your answer. I believe the substitute function looks at the whole cell, while I need to check if the the cell contains a value from the reference table.
– bowernewb
Nov 13 at 22:01
Thank you Alexis for your answer. I believe the substitute function looks at the whole cell, while I need to check if the the cell contains a value from the reference table.
– bowernewb
Nov 13 at 22:01
It checks if it's contained by comparing the length of the string with and without substitution.
– Alexis Olson
Nov 13 at 22:09
It checks if it's contained by comparing the length of the string with and without substitution.
– Alexis Olson
Nov 13 at 22:09
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53254212%2fpower-bi-dax-count-number-of-records-if-contains-string-from-a-reference-table%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
Welcome to Stack Overflow! Please edit to add meaningful code and a problem description here. Posting a Minimal, Complete, and Verifiable example that demonstrates your problem will increase your chances of getting good answers.
– lucascaro
Nov 11 at 23:40