topic Re: Looking up a value associated with aggregated value in DAX Commands and Tips

Looking up a value associated with aggregated value

Anonymous — Mon, 01 Aug 2022 17:24:00 GMT

I've got another basic question here. I have a Tasks table which I've created some Measures for to display to the user the latest completion date of a hardcoded project milestone. One of the Measures is defined as:

Initiation Complete = MAXX ( FILTER ( Tasks, Tasks[Task ID] = "TSK-01" ), Tasks[Finish])

The same Tasks table also has a Milestone Status column. I'm trying to do some conditional formatting based on the status and need to retrieve the status for the milestone with the maximum Finish date, returned by the Initiation Complete measure above.

I thought I could just do LOOKUPVALUE but apparently you can't use it in DirectQuery mode. Thanks for any ideas.

Re: Looking up a value associated with aggregated value

v-easonf-msft — Thu, 04 Aug 2022 07:34:12 GMT

Hi, Anonymous

Create a measure or a field with color name or hex value data like:

Max_date = calculate(max(Tasks[Finish]),ALLEXCEPT(Tasks,Tasks[Task ID]))

Conditional formatting = IF(MAX(Tasks[Finish])=Tasks[Max_date]&&MAX(Tasks[Status])="xxxStatue1","xxxColor1_RGB","xxxColor2_RGB"))

Then use conditional formatting to automatically apply those colors to the background of column 'Status'.

If I misunderstood, please share a sample file for further research.

Best Regards,
Community Support Team _ Eason

Re: Looking up a value associated with aggregated value

Anonymous — Thu, 11 Aug 2022 14:58:47 GMT

Thanks for the reply. I don't think this is quite what I need, but it might be close. Part of it is I'm still quite new to Power BI and Dax. I think that the only additional thing I need in the Conditional Formatting measure is to verify that the project ID is the same as the project ID in the table. How do I do that?

Full story: The table I'm displaying in Power BI looks like this, where Initiation and Go-Live are specific Task IDs:

Project ID	Initiation Complete	Go-Live Complete
123	1/1/22	9/1/22
456	3/1/22	6/1/22
789	2/1/22	10/1/22

Data for this is stored in an underlying table called Tasks, which has data like this:

Project ID	Task ID	Due Date	Status
123	TSK-01	1/1/22	Completed
123	TSK-05	9/1/22	Completed
456	TSK-01	3/1/22	Completed
456	TSK-05	5/1/22	Completed
456	TSK-20	6/1/22	Completed
789	TSK-01	2/1/22	Completed
789	TSK-05	8/1/22	Needs Help
789	TSK-20	10/1/22	On Track

For the "initiation" task, the only task ID is TSK-01. But go-lives can have several predefined task IDs; here I've shown just 05 and 20. In my Power BI visual I'm trying to show the latest due date for a given project and task combination, and I want to shade the table cell containing that date with a color corresponding to the status.

I get the latest date with a measure:

Initiation Complete = MAXX ( FILTER ( Tasks, Tasks[Task ID] = "TSK-01" ), Tasks[Finish]) 
Go Live Complete = MAXX (FILTER(Tasks, Tasks[Task ID] IN {"TSK-05","TSK-20"}),Tasks[Finish])

I just don't know how to create another measure that I can use in conditional formatting to shade the cell since there doesn't seem to be a way besides the MAXX function to keep row context of the project ID in the displayed table.

Re: Looking up a value associated with aggregated value

Anonymous — Fri, 12 Aug 2022 15:13:45 GMT

...and here's the answer I came up with. Hopefully it helps someone.

Go Live Status BgColor = SWITCH(MAXX(TOPN(1,Filter(Tasks,Tasks[Task ID] IN {"TSK-05","TSK-20"}),Tasks[Due Date],DESC),Tasks[Status]),"On Track","#C6EFCE","At Risk","#FFEB9C","Needs Help","#FFC7CE","Not Started","#7F7F7F","Completed","#002060","#FFFFFF")