Power BI DAX - Count number of records if contains string from a reference table












2














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










share|improve this question






















  • 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
















2














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










share|improve this question






















  • 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














2












2








2







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










share|improve this question













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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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


















  • 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












1 Answer
1






active

oldest

votes


















0














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.






share|improve this answer





















  • 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











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
});


}
});














draft saved

draft discarded


















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









0














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.






share|improve this answer





















  • 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
















0














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.






share|improve this answer





















  • 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














0












0








0






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.






share|improve this answer












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.







share|improve this answer












share|improve this answer



share|improve this answer










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


















  • 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


















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







這個網誌中的熱門文章

Xamarin.form Move up view when keyboard appear

Post-Redirect-Get with Spring WebFlux and Thymeleaf

Anylogic : not able to use stopDelay()