Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Looking up a value associated with aggregated value

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.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

...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")

 

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

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'.

veasonfmsft_0-1659597218699.png

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

 

Best Regards,
Community Support Team _ Eason

Anonymous
Not applicable

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 IDInitiation CompleteGo-Live Complete
1231/1/229/1/22
4563/1/226/1/22
7892/1/2210/1/22

 

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

Project IDTask ID    Due Date    Status
123TSK-011/1/22Completed
123TSK-059/1/22Completed
456TSK-013/1/22Completed
456TSK-055/1/22Completed
456TSK-206/1/22Completed
789TSK-012/1/22Completed
789TSK-058/1/22Needs Help
789TSK-2010/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.

Anonymous
Not applicable

...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")

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.