Forum Discussion
Create a filtred table based on an agregation function
Hi,
I have a table formatted as following :
AlertID | status | Date
1 |To analyse | 01/02/2021
3 |To analyse | 03/02/2021
2 |To analyse | 06/02/2021
3 |Closed | 05/02/2021
1 |Treated : alert | 03/02/2021
as you can see, each alert (AlertID) has multiple rows, to keep a path of the treatment.
But with this data i can't just display the number of alerts that are waiting for analysis for example, as the result will be 3 instead of 1 because of data historisation, my first idea has been to try to create a table filtred to take for each alert to take the last record based on the date (take the latest one ) but i didn't figure out how to do it.
i tried : new table = CALCULATETABLE (alerts;filter (alerts;[date] = LASTDATE([date]) ) ) but it didn't worked.
i would like a way to realise effective KPIs, can you please help me?
i'm open to new solutions, not only creating a table.
Thank you.
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"AlertID", Int64.Type}, {"status", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"AlertID"}, {{"Max", each List.Max([Date]), type nullable date}, {"All", each _, type table [AlertID=nullable number, status=nullable text, Date=nullable date]}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"status", "Date"}, {"status", "Date"}), #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each [Max]=[Date]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max", "Custom"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"AlertID", Order.Ascending}}) in #"Sorted Rows"Hope this helps.
13 Replies
wahib_mouhoubi , Try like
new table = CALCULATETABLE (alerts;filter (all(alerts); [AlertID] =max([AlertID]) && [date] = LASTDATE([date]) ) )
new table = CALCULATETABLE (alerts;filter (all(alerts); [AlertID] =max([AlertID]) && [date] = calculate(LASTDATE([date]),allexcept(Table,Table[Date])) ) )
amitchandak Hello, thank you for replying.
when i tried your solutions, on the reduced dataset i wrote above i got this table :
AlertID | status | Date
3 |To analyse | 03/02/2021
3 |Closed | 05/02/2021
and that's not what i want.
Also, i tried it on my real dataset (with a huge amount of data and some additional columns that i considered useless for the treatment i want to make) i got an error :
"A date column containing duplicate dates was specified in the call to function "LASTDATE". This is not supported".
Maybe some alert arrived and was treated the same day, might be possible.
Any idea on how to fix that please?
Thank you.
Anonymous yes exactly !
Hi wahib_mouhoubi ,
Based on your description, you can create a measure as follows.
Measure =
var x1=MAXX(FILTER(ALL(Alert),[AlertID]=SELECTEDVALUE(Alert[AlertID])),[Date])
return
IF(MAX('Alert'[Date])=x1,1,0)Then drag it to the filter on the table visual and select “is 1”.
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Hello,
I tried your solution and it effectively worked on a table visual but when i tried to display a matrix for the count of alerts by status and month ou just a chart for count of alerts by status it didn't worked, i've added the following records :
AlertID | Status | Date
4 | To analyse | 03/02/2021
4 | Closed | 07/02/2021
and the result is above, it kept only the status having the count equal to 1.
My aim is not to display a table but to clean it, keeping only the last status for each alert, that would allow me to know how many alerts has to be treated etc...
Can you please help me?
thank you.
Hello,
My aim is not to display the table but to clean it, keeping only the last status for each alert, that would allow me to know how many alerts has to be treated , how many have been closed etc...
Can you please help me?
thank you.
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"AlertID", Int64.Type}, {"status", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"AlertID"}, {{"Max", each List.Max([Date]), type nullable date}, {"All", each _, type table [AlertID=nullable number, status=nullable text, Date=nullable date]}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"status", "Date"}, {"status", "Date"}), #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each [Max]=[Date]), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max", "Custom"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"AlertID", Order.Ascending}}) in #"Sorted Rows"Hope this helps.
Hi wahib_mouhoubi ,
What I want to achieve is also to let you see the latest status of each individual alert. You can see the result of the operation below. When data is updated, the corresponding results will also be updated to ensure the latest status of each alert.
Maybe I'm not getting what actually you are trying to do.
Could you please share the expected result to have a clear understanding of your question?
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Hi,
Ashish_Mathur sent me the PowerQuery M code i needed, i accepted it as a solution.
Thank you all !
