Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |