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
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!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors