Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
8 months ago
Solved

Filtering a table on the Max date by department

Hi -  I am trying to do the following (I posted something earlier this month and I think the example was incorrect). I would like to pull the max date based upon a specific department.   Input: D...
Need Help
Show and Tell
  • jerryr125's avatar
    jerryr125
    8 months ago

    Thank you everyone for examples and assistance - appreciate it.

    I ended up doing the following:

    - Sort by Department (Ascending) then by date (Decending - putting the most recent date first)

    - Adding ranking logic by Department

    - The result is the most recent date results in a ranking of 1 for each departmnt

    - Filter on the 1 Ranking

    As the data dynamically updates, I will always get the most recent date.

    Thanks - Jerry

MasonMA's avatar
MasonMA
Super User
8 months ago

Hi !

 

I'm not seeing any issue with the solution i provided using your Excel data. If i create a new column with below DAX, or your tested fomular, i have this result. 

 

and in reporting

 

 

 

jerryr125's avatar
jerryr125
Helper IV
8 months ago

Hi MasonMA 

Thank you again for your help.

Ok so my question is this - exactly where do I put that code inthe Power BI / Power Query workflow ?

I already have a lot of code in the workflow when I click on the 'advanced editor' - do I add the code below to the end? 

thanks - Jerry

  • MasonMA's avatar
    MasonMA
    Super User
    8 months ago

    Hi Jerry;

     

    The one i provided and tested is a Power BI approach and you can use without involving Power Query. To add M code in Power Query/Dataflow, you would need to use ronrsnfld and others' Power Query solution.

     

    For that, yes you would need to open the Query editor in Power Query and paste the code inside, 

     

    "

     #"Changed Type" = Table.TransformColumnTypes(LastStep,{{"DEPARTMENT", type text}, {"KPIDATE", type date}, {"KPISCORE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DEPARTMENT"}, {
        {"KPIDATE", (t)=> Table.SelectRows(t, each [KPIDATE]=List.Max(t[KPIDATE])),
            type table[DEPARTMENT=text, KPIDATE=date, KPISCORE=Int64.Type]}}),
    #"Expanded KPIDATE" = Table.ExpandTableColumn(#"Grouped Rows", "KPIDATE", {"KPIDATE", "KPISCORE"})
        
in
    #"Expanded KPIDATE"

    "-- from ronrsnfld 

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    8 months ago

    For entering DAX code, you would navigate to the Table Tools tab on the PBI interface.

    For M code you would use the Power Query Advanced Editor.

     

    I've attached a file showing the two approaches using the same data as you showed in your Excel sheet or you can access it via this link.