topic Re: Tagging required data by several conditions in DAX Commands and Tips

Tagging required data by several conditions

Blouey — Tue, 08 Feb 2022 02:53:17 GMT

Hi all,

The sample data set is shown below for the problem.

I'm trying filter out a data set so that for a given TimePeriod, each Name has only their most recent ModifiedDate retained. I would also need for duplicates of the same Name, TimePeriod and ModifiedDate to be filtered out (eg. first two rows for Dan).

Any advice appreciated.
Many thanks!

Name	TimePeriod	ModifiedDate
Ash	30/01/2022 23:59	30/01/2022 11:10
Ash	30/01/2022 23:59	30/01/2022 11:10
Bob	30/01/2022 23:59	31/01/2022 4:50
Bob	30/01/2022 23:59	28/01/2022 0:55
Bob	30/01/2022 23:59	28/01/2022 0:56
Matt	30/01/2022 23:59	30/01/2022 11:57
Matt	30/01/2022 23:59	30/01/2022 11:58
Dan	30/01/2022 23:59	4/02/2022 0:09
Dan	30/01/2022 23:59	4/02/2022 0:09
Ash	6/02/2022 23:59	3/02/2022 8:00
Ash	6/02/2022 23:59	3/02/2022 8:00
Bob	6/02/2022 23:59	2/02/2022 22:45
Bob	6/02/2022 23:59	3/02/2022 22:31
Bob	6/02/2022 23:59	4/02/2022 5:05
Bob	6/02/2022 23:59	4/02/2022 5:05
Matt	6/02/2022 23:59	4/02/2022 6:11
Matt	6/02/2022 23:59	4/02/2022 6:11
Dan	6/02/2022 23:59	4/02/2022 5:49
Dan	6/02/2022 23:59	4/02/2022 5:49
Dan	6/02/2022 23:59	4/02/2022 5:49

Re: Tagging required data by several conditions

amitchandak — Tue, 08 Feb 2022 03:24:13 GMT

Blouey , You need to add an index column in the power query as all timestamps are the same

you can create a flag like , a new column, and use that in filter

if([index] = maxx(filter(Table, Table[Name] =earlier(Table[Name]) && Table[Modified Date] =earlier(Table[Modified Date]) ), [Index]) ,1,0)

Re: Tagging required data by several conditions

tamerj1 — Tue, 08 Feb 2022 07:24:42 GMT

Hi Blouey
You can start with a calculated column using the followinng code:

Keep? = VAR CurrentName = Data[Name] VAR CurrentTimePeriod = Data[TimePeriod] VAR CurrentModifiedDate = Data[ModifiedDate] VAR FilteredTable1 = CALCULATETABLE ( Data, Data[Name] = CurrentName, Data[TimePeriod] = CurrentTimePeriod, REMOVEFILTERS () ) VAR LastModifiedDate = MAXX ( FilteredTable1, Data[ModifiedDate] ) RETURN IF ( CurrentModifiedDate = LastModifiedDate, "Yes" )

Then you can calculate a new table using this code:

FilteredTable = DISTINCT ( CALCULATETABLE ( Data, NOT ISBLANK ( Data[Keep?] ) ) )

Please let me know if this answers your query.
You can find the Pbi file here https://www.dropbox.com/t/YhBedW8OGb8xrocH

Re: Tagging required data by several conditions

wdx223_Daniel — Tue, 08 Feb 2022 07:42:00 GMT

NewStep=Table.FromRecords(Table.Group(PreviousStepName,{"Name","TimePeriod"},{"n",each Table.Max(_,"ModifiedDate")})[n])