cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Need help with creating filtered column

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: Need help with creating filtered column

Hi @AKB

 

with your data looking like this

Screenshot at Jan. 06 15-09-55.png

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

View solution in original post

1 REPLY 1
Highlighted
Resolver IV
Resolver IV

Re: Need help with creating filtered column

Hi @AKB

 

with your data looking like this

Screenshot at Jan. 06 15-09-55.png

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

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors