counting records based value in a dimension

[mark_plummer-16]

name	ID	MBS	Count of MBS
smith	123	60504	1
smith	123	60505	2
Jones	124	60504	1

moving reports from Oracle Bi to looker and a bit stuck as to how to achieve a table with the count dimension as above - each ID can have many MBS and need a running total  or count by ID only.

In oracle Bi i can just create a dimension to  count MBS by ID  - is there a way to achieve this in looker?

thanks 

[jason_hodson-16]

First, congrats on getting away from Oracle BI!

There seem to be two solutions depending on how big the data set is. The first is simply defining a derived table as a dimension. This allows you to query the data in real time without working about new data being inserted into your data source.

If this creates a performance issue, you can create a persistent derived table (PDT) which is also in that link above.