Counting number of rows where number equals something

[webstark]

Hi team,

Very simple one here.

I have the table below. I have a Site ID, a Pallet ID and a count of the 

number of boxes on pallet (this is a count selection on the dropdown). A pallet can 1 or more boxes on it.

The number of boxes will either be 1 or >= 2.

I just want another 2 measures where the count of all the 1's = something and >=2 = something else. How many times does the one appear and how many times is it 2 or more?

I can do it in T-SQL but I just can't get a Filter working properly.

Help!

[miltenburger]

CountValues =
CALCULATE ( COUNTROWS ( TableName ); TableName[Id] = 1 )

I think this is what you need, just fill in your tablename 

[webstark]

Sorry, it looks like it's just comparing the integer values.

When I put in = 1, I get Blank(), but >= 2 equals everything in the table.

40044114083914688 is greater than 2..everything matches

40044114083914688 = 1, blank().

[v-yulgu-msft · Accepted solution]

Hi @webstark,

Is [Id] in above table in your post a column or measure?

Suppose it is a column, please try these two measures:

palletd 1 =
CALCULATE (
    COUNT ( 'Palletd Table'[Palletd] ),
    FILTER ( ALL ( 'Palletd Table' ), 'Palletd Table'[Id] = 1 )
)

palletd 2 =
CALCULATE (
    COUNT ( 'Palletd Table'[Palletd] ),
    FILTER ( ALL ( 'Palletd Table' ), 'Palletd Table'[Id] >= 2 )
)

If it doesn't meet your request, please post source data in data view and show us your desired output.

Best regards,
Yuliana Gu