Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

Thanks in advance

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.

Community Champion

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)

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors