Forum Discussion
Flagging Most Recent Transaction
Goal: Create 2 calculated columns where it will create 1 flag for either the Latest Outstanding Status or Latest Resolved Status. 1 Store = 1 Status Flag (either Outstanding or Resolved)
Problem: I am successfull at flagging the most recent status but each store can only have 1 flag. My current output for each calculated column will flag the most recent Outstanding/Resolved status but for stores with both, it will flag both. How can I create the flag where if there is one Resolved status, the entire store is classified as Resolved and flag the most recent one. Same with outstanding. If there are zero resolved status' for the store, the entire store is classified as Outstanding and flag the most recent one.
Below are the following: DAX Calculated Column + Sample Data with Expected Output Flags (notice how each store has only 1 flag).
Any advice is greatly appreciated.
DAX Resolved Calculated Column Flag >>> (The exact same was created for Outstanding, only thing that changed was "Store[Status] = Resovled" to "Store[Status] <> Resolved")
IF(
[Date]
= CALCULATE(
MAX( [Date] ),
ALLEXCEPT( Store, Store[store_id] ),
Store[Status] = "Resolved"
), 1
)
Sample Data with Expected Output Flags
| store_id | trans_id | status | date | latest_outstanding_flag | latest_resolved_flag |
| 1 | 100 | New | 1-Jan-21 | ||
| 1 | 101 | New | 12-Jan-21 | ||
| 1 | 102 | New | 20-Feb-21 | 1 | |
| 2 | 103 | New | 1-Jan-21 | ||
| 2 | 103 | Resolved | 2-Jan-21 | 1 | |
| 3 | 104 | New | 5-Jan-21 | ||
| 3 | 104 | Resolved | 10-Jan-21 | ||
| 3 | 105 | New | 2-Feb-21 | ||
| 3 | 105 | In Progress | 5-Feb-21 | ||
| 3 | 105 | Resolved | 6-Feb-21 | 1 | |
| 4 | 106 | New | 12-Jan-21 | ||
| 4 | 106 | Resolved | 13-Jan-21 | 1 | |
| 4 | 107 | New | 1-Mar-21 | ||
| 4 | 107 | In Progress | 2-Mar-21 | ||
| 5 | 108 | New | 10-Mar-21 | ||
| 5 | 108 | In Progress | 11-Mar-21 | ||
| 5 | 109 | New | 16-Mar-21 | ||
| 5 | 110 | New | 22-Mar-21 | 1 |
4 Replies
Anonymous thank you so much for your support! This was exactly what I needed. Apologies if I was vague or confusing with my description. My only problem was with the latest_outstanding_flag column. The latest_resolved_flag gave me the information needed but I was not able to isolate what your solution provided for the other calculated column.
Once again, thank you!
Hi Anonymous
It depends on how you analyse /visualize further with it. Adding it as a DAX calculated column, mark -1 or 1
Column = VAR T1 = FILTER(Store,Store[store_id]=EARLIER(Store[store_id])) RETURN IF(CONTAINS(T1,Store[status],"Resolved"), IF([date]=MAXX(FILTER(T1,[status]="Resolved"),[date]),1), IF([date]=MAXX(T1,[date]),-1))For most of the cases, prefer M for column, here is one way
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLBCsIwDIZfRXpeoUnXqi8gTFDE69hhYvEiG3Sgr++sbMlMd8nl+/jzE1LXClShwJhxnsN7nAb0se00gmqKicJMASXFmaLRh3CbKCZqV5KJXsPQP1/h/lUW8TYpJQW4PGUBYKTiKAB5P6JVt7nE/hHDMPzWZC3e03OlTIpfORJR3tNKZcsvdWqjpH89kVsuWTsqYfJ0mQEgrT1leEGBPgVpf/MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [store_id = _t, trans_id = _t, status = _t, date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"store_id", Int64.Type}, {"trans_id", Int64.Type}, {"status", type text}, {"date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"store_id", "status"}, {{"maxDate", each List.Max([date]), type nullable date}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"store_id"}, {{"allrows", each _, type table }}), #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "res_Date", each List.Max( Table.SelectRows([allrows], each [status] = "Resolved")[maxDate])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "max_Date", each if [res_Date] = null then List.Max([allrows][maxDate]) else null), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"store_id"}, #"Added Custom1", {"store_id"}, "Added Custom1", JoinKind.LeftOuter), #"Added Custom2" = Table.AddColumn(#"Merged Queries", "Custom", each if [date] = [Added Custom1][max_Date]{0} then -1 else if [date]=[Added Custom1][res_Date]{0} then 1 else null) in #"Added Custom2"