cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## Create count measure

Hey,

I'm trying to create a measure that will count all the numbers at the column, but only those who greater then 0 (the measure will NOT count null cells and 0 number).

I tried the CALCULATE function with filter but unfortunately I didnt succeed.

The name of the column is "Users".

Thanks for helping!

1 ACCEPTED SOLUTION
Solution Sage

Hello, Im not sure if you'd expect an outcome of 91, or 20 with the shown data; so i made both measures.

If you'd expect to see 91 (thats the total of the values) you can use:

``Measure = SUM(TableUsers[Users])``

If you'd expect to see 20 (the count of rows where there is no NULL or 0 you can use:

``Measure 2 = CALCULATE(COUNT(TableUsers[Users]),TableUsers[Users] > 0)``

Goodluck,

Eric.

Quality over Quantity

4 REPLIES 4
Super User

@Anonymous - Try:

``````Measure = COUNTROWS(FILTER('Table',[Users]<>0))

Measure Distinct Count = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Users]<>0),"Users",[Users])))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Solution Sage

Hello, Im not sure if you'd expect an outcome of 91, or 20 with the shown data; so i made both measures.

If you'd expect to see 91 (thats the total of the values) you can use:

``Measure = SUM(TableUsers[Users])``

If you'd expect to see 20 (the count of rows where there is no NULL or 0 you can use:

``Measure 2 = CALCULATE(COUNT(TableUsers[Users]),TableUsers[Users] > 0)``

Goodluck,

Eric.

Quality over Quantity

Anonymous
Not applicable

Yeah that's what I meant,

Thanks!

Super User

@Anonymous , Try like

count of user = countx(summarize(Table,Table[user], "_1", [Measure]+0),[user])
count of user = countx(values(Table[user]),[Measure]+0)

Just for ref

count of user >0 value= countx(filter(summarize(Table,Table[user], "_1", [Measure]),[_1]>0),[user])