Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create new table from other table and keep rows with most recent dates filtered by unique values

Hey everyone,

 

I just figured out that I need to reduce my main table to a smaller, new table with just the most recent rows. 

These rows should contain only the most recent rows depending on the unique values on the column "CAD-NR.".

 

As an example, please have a look on this picture:

 

 

I tried with summarize and groupby, but I didn't succeed:

 

GoupBy Test =
GROUPBY(Messdaten_Entpivotiert;Messdaten_Entpivotiert[CAD-NR.];Messdaten_Entpivotiert[Attribut];"Attribut";maxx(CURRENTGROUP();Messdaten_Entpivotiert[Datum].[Date])
)
 
---> I get just some columns, I would need all columns from the main table
 
GroupEnCADNR = SUMMARIZE(Messdaten_Entpivotiert;Messdaten_Entpivotiert[CAD-NR.];"Datum";MAX(Messdaten_Entpivotiert[Datum]);"Typ";MIN(Messdaten_Entpivotiert[Typ]))

 

--> Since I always have to choose a min or max, also for text values, I get wrong values

 

Thnak you for your help I am stuck 

Need Help
Tips and Tricks
Tutorial Requests
  • AlB's avatar
    AlB
    6 years ago

    Hi Anonymous 

    NewTable =
FILTER (
    Table1,
    Table1[Date] = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[CAD-NR] ) )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

6 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    NewTable =
FILTER (
    Table1,
    Table1[Date] = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[CAD-NR] ) )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Two things to consider.  If you will never need these older rows, this should be done in the query editor on your main table, so you are not storing some of your data twice.  Or, you can likely do the needed calculations with a measure and also not create another table with your model.  Would either approach be acceptable?

     

    If you still need a DAX table created, it can likely be done with an expression that creates a virtual table of your CAD-NR values and the max date for each, and then doing a join of that table with your original table to get your desired result.

     

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey mahoneypat ,

       

      thanks for your advices that conscern the whole process.

       

      I would prefer to solve this challenge with a virtual table and only measures, but I don't know how.

       

      Could you give me some information about how I could implement that?

      Greetings from Austria 🙂

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous 

        You can use the same code I shared earlier to create the virtual table as a VAR within in a measure. Do note the code might need some tweaking to account for filter context, which was not present when we created the calculated table:

         

        Measure =
VAR fTable_ = //This is the virtual table, with same code as earlier
FILTER (
    Table1,
    Table1[Date] = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[CAD-NR] ) )
)
RETURN
//and then use the virtual table here as needed

         

         Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.

        Cheers