Need help with creating filtered column

[AKB]

Hi all, new to Power BI and I'm really stuck. We collect telemetry data from our users periodically. It comes in as key value pairs and we insert it into a DB basically like so:

CUSTOMER_ID  INSERTION_ID  KEY  VALUE
1            1             RULE BOB
1            1             TIME XXX
2            17            RULE JIM
2            17            TIME YYY
1            45            RULE BOB
1            45            TIME ZZZ

Every time a bunch of key/value pairs are inserted they use the same INSERTION_ID, which is globally unique.

I wanted to make a visualisation that showed me the most recent data for customers. What I thought I would need to do is first make another table which had DISTINCT CUSTOMER_ID, along with the highest INSERTION_ID for that CUSTOMER_ID. That way I could then use the INSERTION_ID from that table as the axis. 

What I haven't been able to do is find the highest INSERTION_ID and put it into a new column in that table. I've tried FILTER, SELECTCOLUMNS along with MAX but MAX needs a column and not a table that is returned by the former 2.

Does anyone know what I can do here? Is there a better way to achieve what I want? The visualisation is so I can see the # of RULEs that each customer has but only for the most recent submission from that customer. I realise I can add a new record to the DB at insertion time but I was hoping I could do this dynamically.

Thanks

A

[BetterCallFrank · Accepted solution]

Hi @AKB

with your data looking like this

you can create a measure like this:

NumRuleMaxInsertId = 
VAR CustID = MAX( Table1[Cust] )
VAR InsID = MAX( Table1[Ins] )
RETURN
CALCULATE( COUNTROWS( Table1 ), 
  Table1[Cust] = CustID, 
  Table1[Ins] = InsID, 
  Table1[Key] = "RULE"
)

HTH,

Frank