Forum Discussion
How to write DAX countif
Hi Danielwood
You can use a dax measure :count_sold_delivered =CALCULATE(DISTINCTCOUNT('Table'[product name]),'Table'[sold]="Y" &&'Table'[status]="delivered")Pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Danielwood
1. Yes && is equivalent to "And" in condition.
2. if your question is about searching strings in other text you can use the dax function :
"Search"
https://learn.microsoft.com/en-us/dax/search-function-daxor "Find" :
https://learn.microsoft.com/en-us/dax/find-function-daxIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi Danielwood
You can use one of the functions that I mentioned in my previous response.For example :
count_sold_delivered =CALCULATE(DISTINCTCOUNT('Table'[product name]),'Table'[sold]="Y" &&SEARCH("deli",'Table'[status],,0)<>0)The updated pbix is attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly1. add 0 to the measure :
count_sold_delivered =CALCULATE(DISTINCTCOUNT('Table'[product name]),'Table'[sold]="Y" &&SEARCH("bla bla bla",'Table'[status],,0)<>0)+02. The same but you need "= 0"
count_sold_delivered =CALCULATE(DISTINCTCOUNT('Table'[product name]),'Table'[sold]="Y" &&SEARCH("shiped",'Table'[status],,0)=0)The updated pbix is attached again 🙂
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
That's really helpful, thank you. So each time I want to build on this would it be an additional && for each extra criteria?
Also, if I wanted to count an entry when a word appeared in the string, in excel it you be *&* word *&*. Is there something comparable I could use? It's not the end of the world if this isn't an option
Hi Danielwood
1. Yes && is equivalent to "And" in condition.
2. if your question is about searching strings in other text you can use the dax function :
"Search"
https://learn.microsoft.com/en-us/dax/search-function-dax
or "Find" :
https://learn.microsoft.com/en-us/dax/find-function-dax
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Perfect, thank you.
The second question was still about the count function, so count where maybe the string of text is "item has been shipped", if the string includes the word shipped. In addition to the above critera
Hi Danielwood
You can use one of the functions that I mentioned in my previous response.For example :
count_sold_delivered =CALCULATE(DISTINCTCOUNT('Table'[product name]),'Table'[sold]="Y" &&SEARCH("deli",'Table'[status],,0)<>0)The updated pbix is attachedIf this post helps, then please consider Accepting it as the solution to help the other members find it more quicklyAmazing, thank you.
Two last questions I promise!
1) if the count finds nothing, how do I return a 0?
2) with the search function, what do I type to say exclude anything that says shipped?