Create a filtred table based on an agregation function

[wahib_mouhoubi]

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.

[v-yuaj-msft]

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.

[wahib_mouhoubi]

@v-yuaj-msft 

Hi, 

@Ashish_Mathur sent me the PowerQuery M code i needed, i accepted it as a solution.

Thank you all ! 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[wahib_mouhoubi]

@Ashish_Mathur 

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.

[Ashish_Mathur · Accepted solution]

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.

[wahib_mouhoubi]

That's exactely what i was looking for, thank you ! 

[Ashish_Mathur]

You are welcome.

[v-yuaj-msft]

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.

[wahib_mouhoubi]

@v-yuaj-msft 

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.

[v-yuaj-msft]

Hi @wahib_mouhoubi ,

Is the following result you want? 

Best Regards,

Yuna

[wahib_mouhoubi]

@v-yuaj-msft yes exactly !

[amitchandak]

@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])) ) )

[wahib_mouhoubi]

@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.