cancel
Showing results for
Did you mean:
New Member

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

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?

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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)

Announcements