Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

PowerBI Countif Top to Row Question

I've been working with PowerBI for about a year or so now, and its such a powerful tool.  But as an Excel native there are still things I find easier in Excel to do.  One process I haven't been able to figure out in PowerBI is how to count from the top of a sorted dataset to the current row how many instances of a data point there is.

 

Example:

My dataset is sorted by date/time.

 

 

I want to create a key, where on this dataset sorted by the date field "LegEnd" in ascending order we count to that row how many times the Tractor_ID shows up.

 

Tractor 1072 shows up in the dataset twice, on the first row it shows up I want the count to show "1", and the second time I want the count to show "2".  Then I will concatenate it with the Tractor_ID to create a squencing key "1072-1" and "1072-2" and so on.

 

In Excel I used to be able to simply create this formula in a new column and copy it down:

 

= [Tractor_ID] & "-" & countif( [Tractor_ID]A$1:Ax, [Tractor_ID] )

 

Can anyone help me with this?

 

 

2 Replies