Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |