cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wahib_mouhoubi
Helper I
Helper I

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.

 

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
v-yuaj-msft
Community Support
Community Support

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.

022501.gif

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

@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
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yuaj-msft
Community Support
Community Support

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:

v-yuaj-msft_0-1614042045478.png

 

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.

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

pbi.PNG

v-yuaj-msft
Community Support
Community Support

Hi @wahib_mouhoubi ,

 

Is the following result you want? 

022201.gif

 

 

 

 

 

 

 

 

 

Best Regards,

Yuna

@v-yuaj-msft yes exactly !

 

 

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors