How to write DAX countif

[Danielwood]

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.

[Ritaf1983]

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

[Danielwood]

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 

[Ritaf1983]

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

[Danielwood]

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

[Ritaf1983]

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 attached 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Danielwood]

Amazing, 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?

[Ritaf1983 · Accepted solution]

@Danielwood 

1. add 0 to the measure :

count_sold_delivered =

CALCULATE(DISTINCTCOUNT('Table'[product name]),'Table'[sold]="Y" &&SEARCH("bla bla bla",'Table'[status],,0)<>0)+0

2. 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