Getting DAX to return 0 instead of Blank

Question

Using PowerPivot and DAX Countrows along with filter option with a column that has six possible expressions. (Excellent, Good, Acceptable, Not good, Poor and Blank). Post count, I am calculating Satisfaction percentage by Dividing Excellent and Good by the Total responses. My problem is that the countrows function does not take blank as 0 and thus screws up my calculation. How do I get around this?

Answer 1

A simple yet effective trick I've always used: add + 0 to your measure.

Data with only "A" and "B":

Before:

After adding + 0:

Answer 2

You don't want COUNTROWS because a row with blank is indeed a row.

One way is to create a weighting table with blank not weighted at all. This makes the evaluation explicit and easy to change. With a relationship between the Responses table and the Response Evaluation, Satisfaction is just an average (which you can display as a percentage if desired).

Response Evaluation = DATATABLE (
    "Response", STRING, 
    "Weight", DOUBLE,
    { 
        { "Excellent", 1 }, 
        { "Good", 1 }, 
        { "Acceptable", 0 }, 
        { "Not good", 0 },
        { "Poor", 0 },
        { "", BLANK() }
    } 
)

---

Satisfaction = AVERAGEX(Responses, RELATED('Response Evaluation'[Weight]))

---

So with the following sample data, Satisfaction is 40%.

Responses = DATATABLE (
    "Response", STRING, 
    { 
        { "Excellent" }, 
        { "Good" }, 
        { "Acceptable" }, 
        { "Not good" },
        { "Poor" },
        { "" },        
        { "Excellent" }, 
        { "Good" }, 
        { "Acceptable" }, 
        { "Not good" },
        { "Poor" },
        { "" },
        { "" },
        { "" },
        { "" },
        { "" },
        { "" }
    } 
)