Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to Solution.
1. add 0 to the measure :
2. The same but you need "= 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
Hi @Danielwood
You can use a dax measure :
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-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 :
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?
1. add 0 to the measure :
2. The same but you need "= 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
User | Count |
---|---|
120 | |
72 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |