Using MAX to generate summary visuals

[badgerpackerfan]

Background:  I'm an engineer with no prior experience with this tool or data visualization in general.  I have some data that I want to present on a dashboard that shows our current status.  I've created a few visuals but haven't figured out how to generate the charts to only visualize the latest data.  What I really want is to grab the latest (which maybe different) score from each tb_id to cacluclate the most current average for the 2 bar charts.

Here is my powerbi workbook:

https://drive.google.com/file/d/1oItnqZTkylQnQSOHH1tp5NhVrJbHspFh/view?usp=sharing

[V-pazhen-msft]

@badgerpackerfan

I really don't get it."Most current average for 2-bar charts, do you mean you want average score by 3 groups? (tb id, entity ID, and subsystem ID)

But if you want to filter the visuals to the most recent date, you can use the TOPN filter.

Paul Zheng Community Support Team _
If this post helps, please consider accepting it as the solution to help other members find it more quickly.

[badgerpackerfan]

I tried the expression above and it looked close but wasn't exactly what I wanted as one of the data points had a score over 100 which isn't possible.  Is there a way to get the table output of a DAX expression to see what it does?  It was close but I'm not sure how to debug it.  I'd like to see what each part of the expression pulls out of the original table.

Top N didn't work the way I wanted to because it only looks at timestamp.  You'll notice one of the rows has 5/2/2020 instead of 5/1/2020.  When I did that, Top N only returned the 5/2 entry.

Below is a tablee of what I want to get out for the visuals.  Here's some pseudo code:

foreach(feature_id)

  find row with latest timestamp foreach(tb_id)

tb_id	feature_id	score	timestamp	subsystem_id
22	1	55	5/1/20 0:00	4
23	1	65	5/1/20 0:00	4
24	1	67	5/1/20 0:00	4
27	1	70	5/2/20 0:00	4
31	1	78	5/1/20 0:00	5
19	1	69	5/1/20 0:00	3
37	1	72	5/1/20 0:00	3
38	1	75	5/1/20 0:00	3
6	5	84	5/1/20 0:00	1
19	5	75	5/1/20 0:00	3
20	5	86	5/1/20 0:00	3
21	5	89	5/1/20 0:00	3
27	5	99	5/1/20 0:00	4
36	5	83	5/1/20 0:00	3
37	5	79	5/1/20 0:00	3
38	5	82	5/1/20 0:00	3

[camargos88]

Hi @badgerpackerfan ,

Try this measure:

_AVX =

AVERAGEX(ADDCOLUMNS(SUMMARIZE('status'; 'status'[tb_id]; "Max"; MAX('status'[timestamp])); "Score"; CALCULATE(SUM('status'[score]); FILTER('status'; 'status'[tb_id] = EARLIER('status'[tb_id])))); [Score])

Did I answer your question? Mark my post as a solution!
Ricardo