Reply
Member
Posts: 98
Registered: ‎06-01-2018

count of id

[ Edited ]

i got a csv file with an id and transaction i need to count for each id how many transaction line there isCapture.PNG

in this case for id 8 should be 5

an easy task but i can find out right now im stuck

count row same id

Member
Posts: 107
Registered: ‎09-10-2018

Re: count of id

The simplest way would be to create a measure like this

Measure = COUNTROWS(Table1)

 

On the report canvas, drag the id field and this measure onto a table visualization

Highlighted
Member
Posts: 125
Registered: ‎01-21-2018

Re: count of id

@snifer

You can use something like this 

 

Column =
Var new = Table1[id]
Var Count1 = CALCULATE(
COUNTROWS(Table1),
all(Table1),
Table1[id] = new
)
Return Count1

Member
Posts: 98
Registered: ‎06-01-2018

Re: count of id

[ Edited ]

@vmakhija

 

 

Column =
Var new = transactions[CustomerID]
Var Count1 = CALCULATE(
COUNTROWS(transactions),
all(transactions),
transactions[CustomerID] = new
)
Return Count1))

 

name of table column : transactions[CustomerID]

 

 I got an error

 

The syntax for ')' is incorrect. (DAX(Var new = transactions[CustomerID]Var Count1 = CALCULATE(COUNTROWS(transactions),all(transactions),transactions[CustomerID] = new)Return Count1)))).

 

 

Member
Posts: 125
Registered: ‎01-21-2018

Re: count of id

@snifer

Please make sure you have correct opening and closing brackets in your column definition.

I created the column and got no errors 

 

Column =
Var new = Table1[ID]
Var Count1 = CALCULATE(
COUNTROWS(Table1),
all(Table1),
Table1[ID] = new
)
Return Count1

Member
Posts: 98
Registered: ‎06-01-2018

Re: count of id

count =
Var new = transactions[CustomerID]
Var Count1 = CALCULATE(
COUNTROWS(transactions),
all(transactions),
transactions[CustomerID] = new
)
Return Count1

 

A single value for column 'CustomerID' in table 'transactions' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

@vmakhija

 

 

Member
Posts: 125
Registered: ‎01-21-2018

Re: count of id

@snifer

 

are you creating it as column or measure?

The example I gave is for creating a column and not a measure.

Member
Posts: 98
Registered: ‎06-01-2018

Re: count of id

[ Edited ]

@vmakhija

I tried both but same error

Community Support Team
Posts: 7,323
Registered: ‎05-02-2017

Re: count of id

Hi @snifer,

 

That's weird. Can you share some snapshots?

Please also try this one. It's a calculated column.

 

Column =
CALCULATE (
    COUNT ( transactions[CustomerID] ),
    ALLEXCEPT ( transactions, transactions[CustomerID] )
)

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.