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 Jerry;

 

I was not able to reproduce your errors either unless you can share your real 'KPIDATE' or more data so that we can play with. Tried with some cases like if your 'KPIDATE' has nulls, Non-date values etc. but all works. 

  • 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