Forum Discussion

wahib_mouhoubi's avatar
wahib_mouhoubi
Helper I
5 years ago
Solved

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  ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    wahib_mouhoubi
    Helper I
    5 years ago

    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.