Forum Discussion
How to write DAX countif
Hi, my data table has lots of static information, but I want to do some countif functions to make reporting easier. I've tried using a text box, then values, and typing out the question but it's very hit and miss, so thought maybe DAX is the way forward. Problem is, I don't know how to write them!
Column A has the product name. These are duplicated.
Column B has sold, where the only answer is Y or N.
Column C is status, open, closed, being delivered.
How would I do a calculation that says number of UNIQUE product names, where sold is Y and status is being delivered?
I think if I could get some help on the first one I can try to work out the rest. Unfortunately I can't share a table due to sensitive information.
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
7 Replies
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
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-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
