Matrix that shows rows based on most recent value criteria.

[mabCOLONEL]

I cant share much of my report, because it has sensative insurance and personal information.  

We work in automotive.  I have a large data table that collects reviews of repairs and processes per vehicle.  Currently I am trying to create a type of 'queing' system.  Some vehicles may not be worth it to repair.  So, when my team finds these vehicles, they tag their 'review' (A) with a checkbox.  This check box is to alert another team that we need to perform an additional step (B) on this specific vehicle.  

Working in dynamic sheets was sort of getting it done, but lots of duplications and other such stuff.  So I figured I could get this on power bi.  So what I did in my old dynamic sheets was create an automation to copy the rows when review "A" gets tagged.  So a tagged review copies to another 'queue' sheet.  This sheet looks back at my live data for the same vehicle, and once it finds that the tagged line is no longer the most recent, and that now a line with review "B" is most recent (index function), it filters out that result.  So, basicaly, I need to see row A, until row B happens, then dont show it anymore.  

A & B are values in two different coilumns.  So it looks sort of like below....

Date	Vehicle ID	Other data	Tag	Action	Other data	Other data	other data
12/13	234		A
12/12	123			B
12/12	789			D
12/11	123		A

So, in the above example, Vehicle "123" would have been on my queue matrix on 12/11 but then it would filter out on 12/12 because action B was completed.  Vehicle "234" would still be on the queue matrix.  Vehicle "789" does not apply to the condition.  

Currently, I can filter out Action B items by using a measure to tag the 'most recent' row for the Vehicle ID.  

Most Recent = VAR maxd =

    CALCULATE (

        MAX ( 'Review Table'[date] ),

        ALLSELECTED ( 'Review Table' ),

        VALUES ( 'Review Table'[Vehicle ID] )

    )

RETURN

    IF ( MAX ( 'Review Table'[date] ) = maxd, 1, BLANK () )

So my matrix for Action B items is first filtered to only show "1" for most recent row measure, then filtered to only show Action items with a B...so that gives me only the vehicle ID when its most recent row has a B in that column. 

Now I need to create a matrix that shows items tagged with "A", but only if there is NOT a more recent row with "B".
This works ok for the B items, but not at all for the A items.  I used the most recent measure to filter the matrix like before, and only show items where A is tagged and do not include items where the action is B.  But this does not work at all.  It shows items that are tagged with A, but then I find there is a more recent row with a B on it.  

I feel like the measure is not working the way i think it is and is a bit too flexible and it is simply trying to show me the most recent A tag.  I was considering creating a custom column in the source table to really  tag the most recent row with that Vehicle ID, but cant get it right.  All 'most recent' functions I found are for measures or DAX and do not work in the power query custom column.  If I was in Excel, I would do a collect max function to collec tthe max date per vehicle, then if the max date = the row date, tag a "1" or "y" etc.  Can someone help me write those columns or provide a more straight forward approach?  

Example:

Date	Vehicle ID	Tag	Action	Misc	Misc	Max Date	Is max
12/13	123					12/13	Y
12/13	234					12/13	Y
12/12	234					=max(collect(Date, Vehicle ID)	=if(Max Date=Date, "Y", "")
12/11	123					12/13

Thanks!

[v-jianboli-msft]

Hi @mabCOLONEL ,

Do you want to selecte the IDs that have Tag A and no action B?

If so, please try:

Flag = 
var _a = SELECTCOLUMNS('Table',"Tag",[Tag])
var _b = SELECTCOLUMNS('Table',"Action",[Action])
return IF("A" in _a&&NOT("B" in _b),1,0)

Then apply it to the visual level filter:

Final output:

If you want to add a column to find if the date is the max date, you can try:

Is MAX = 
var MaxDate = MAXX(FILTER('Table',[Vehicle ID]=EARLIER('Table'[Vehicle ID])),[Date])
return IF([Date]=MaxDate,"Y")

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mabCOLONEL · Accepted solution]

Thanks for the suggestion.  I ended up grouping by vehicle ID, copying the grouped columns, using one to expand only the max date and then expanded the rest.  Then, added custom column, if row date=max date, "1" else null.  So I was able to filter to only the absolute most recent info at a table level instead of a filter level.  

The filters via measure were causing a problem because the application of the ilter seemed to be arbitrary...as in, when I wanted only the max date with the tag on line "A", it was grabbing the tag first, then returning the most recent with the tag.  I had to 'remove' all older rows from the table first.  

Once I was able to understand how the grouping works, it went smoothly.  I forget how often using duplicate tables can be helpful.  I also duplicated the entire data set so, while only showing the most recent row in the matrix, I also added a smaller visual to show the history of that vehicle ID when selected in the matrix.  

Worthy of note, I also ended up with a boolean error when publishing to the online app.  One of my tag columns didnt like being a number/text, and needed to be True/False to avoid confusing the app level data refresh.  Double check your column data types everyone.