Calculating a value in a cell except for Blanks and N/A

[treack]

Hi, I am trying to calculate a Measure in power BI that returns me a count if a value is present in a cell, except for N/A and blanks, similar to the Total Fruit & Vegetables column below:

Fruit	Vegetables	Total Fruit & Vegetables
Apple	N/A	1
Orange	Cucumber	2
	Celery	1

Does anyone know what is the line of code for this in DAX Power BI?

Thanks in advance

[v-lionel-msft · Accepted solution]

Hi @treack ,

Or Create a measure like this.

Measure = 
VAR __fruit = 
COUNTAX(
    FILTER( Sheet4, Sheet4[Fruit] <> BLANK() && Sheet4[Fruit] <> "N/A" ),
    [Fruit]
)
VAR __vegetables = 
COUNTAX(
    FILTER( Sheet4, Sheet4[Vegetables] <> BLANK() && Sheet4[Vegetables] <> "N/A" ),
    [Vegetables]
)
RETURN
__fruit + __vegetables

Best regards,
Lionel Chen

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

[harshnathani]

Hi @treack ,

Steps to follow.

You will need to add a Index Column.

Then Select the Index Column and Unpivot ALL other Columns

Then use the measure

Mea = COUNTROWS(FILTER('Table','Table'[Value] <> BLANK() && 'Table'[Value] <> "N/A"))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)