topic Re: How to aggregate some columns based on values in other columns in DAX Commands and Tips

How to aggregate some columns based on values in other columns

Anonymous — Fri, 29 Jul 2022 20:00:04 GMT

I'm still pretty new to Power BI so apologies if this is super basic. I've got a table with data like this:

Project ID	Task ID	Date
123	1	4/1/22
123	5	6/1/22
123	29	7/1/22

I'm trying to show in Power BI a table that looks like this:

Project	Task 1	Go Lives
123	4/1/22	7/1/22

In other words, for each project show the latest date associated with Task 1 and the latest date associated with any go live task. The go live tasks have predefined IDs. I've been using a Matrix visualization, and I can pivot the task IDs I want with the "show values as column headers" option. But I'm not sure how to write the DAX I presume I'd need to create a new "Go Lives" column with the latest associated date for the given tasks.

Thanks!

Re: How to aggregate some columns based on values in other columns

tamerj1 — Fri, 29 Jul 2022 20:20:22 GMT

Hi Anonymous
Do you have a list of the go live task id's in a seperate table or you would like to hard code these id's?

Re: How to aggregate some columns based on values in other columns

Anonymous — Fri, 29 Jul 2022 20:23:14 GMT

Hi tamerj1 , I can just hardcode them. There are only a handful.

Re: How to aggregate some columns based on values in other columns

tamerj1 — Fri, 29 Jul 2022 20:31:17 GMT

Anonymous
But I'm a little bit confused whay you want to use a matrix visual. As in this case you would need a table visual. Then you can use

Task 1 Date = MINX ( FILTER ( TableName, TableName[Task ID] = 1 ), TableName[Date] )

Go Live Date = MAXX ( FILTER ( TableName, TableName[Task ID] IN { 29, 30, 31 } ), TableName[Date] )

Re: How to aggregate some columns based on values in other columns

Anonymous — Fri, 29 Jul 2022 21:01:06 GMT

Ahh, thanks! I had thought that to pivot it I needed to use a matrix but this seems easier. I'll give it a try in a bit.