topic Re: Filtering a table to a new table in DAX Commands and Tips

Filtering a table to a new table

ConnorH — Fri, 30 Oct 2020 21:18:50 GMT

I am not really sure how to go about an issue I came across.

I have a table called status containing columns such as:

Project ID	Status Extract	Status Change Date	Created date
13568	3.0	7/15/20	7/10/20
13568	4.0	7/16/20	7/10/20
15598	1.0	7/18/20	7/16/20
15598	2.0	7/20/20	7/16/20
13568	3.0	7/20/20	7/10/20
13772	4.0	7/30/20	7/10/20

I am trying to filter this table into another table or create another column in this table which will allow me to filter out blanks. The project may have the same status twice but my goal is to have a project with no duplicate status and take the most recent date for the status changed date.

So a a custom column would look like this. Where the first row with the new column is left blank because there is a more recent date for that status with that project ID.

Project ID	Status Extract	Status Change Date	Created date	Edit Status Change Date
13568	3.0	7/15/20	7/10/20
13568	4.0	7/16/20	7/10/20	7/16/20
15598	1.0	7/18/20	7/16/20	7/18/20
15598	2.0	7/20/20	7/16/20	7/20/20
13568	3.0	7/20/20	7/10/20	7/20/20

My other idea fold be to somehow pull this data into a new table with a filter but I don't now how I would do that.

Re: Filtering a table to a new table

W_G_Ryan — Sat, 31 Oct 2020 01:35:30 GMT

I'm not totally sure I follow the goal, but if you're familiar with Lag and Lead in SQL, is the idea that if there's a Lead value on the date, to leave the column blank? If so I think this is what you're looking for. Still new myself but I used this a while ago and I think this will work if I follow ask correctly:

https://community.powerbi.com/t5/Desktop/Lead-and-Lag-in-DAX/td-p/649162

Re: Filtering a table to a new table

ConnorH — Mon, 02 Nov 2020 20:22:20 GMT

I was able to pull the Latest date where ProjectID=ProjectID and Status=Status. I still do not know how to set this equation up to only keep one value per status per project ID.

ProjectID	Status Extract	Modified	Last Status Date (New column)
34631	2.1	6/1/20
34631	2.1	6/4/20	6/4/20
34631	4.0	6/20/20	6/20/20

"Last status date =

var _ProjectID = 'Status'[ProjectID]

var _Status = 'Status'[Status Extract]

Var _StatusDate = 'Status'[Modified]

var _LaterDate = MAXX(FILTER('Status','Status'[ProjectID] = _ProjectID && 'Status'[Status Extract] = _Status), 'Status'[Modified])

Return _LaterDate"

this equation will make the table like this:

ProjectID	Status Extract	Modified	Last Status Date (New column)
34631	2.1	6/1/20	6/4/20
34631	2.1	6/4/20	6/4/20
34631	4.0	6/20/20	6/20/20