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

[Anonymous]

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 

[mahoneypat]

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]

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]

@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 

[Anonymous]

Hi,

@mahoneypat thx!

I get an error if I just want to return this table or the sum of a column of this table.

How should I work with such a virtual table?

BR

[AlB]

@Anonymous 

I assume you are addressing me and not @mahoneypat 

I would need more details on what you are trying to do exactly how you are using the measure. Try this measure in a card visual to sum the contents of one of the  (numerical) table columns: 

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
SUMX(fTable_, [NameOfColumnToSum]) //Use the name of your column here, instead of "NameOfColumnToSum" 

Bear in mind that a measure cannot return a table, only scalars.

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 

[AlB · Accepted solution]

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