Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Solved! Go to 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.
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.
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)
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.