Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Bringing in the first string in a list, based on matching values from two columns


This is how two of my columns are set up. The ID# is unique and any MMM named ID #'s have nothing in the Link ID # because they are the 'parent' or 'starting' task. Any tasks marked with the same string in the Link ID # column, are associated under that ID # (I have one data set and a duplicate of the same dataset for drilling through).


My Matrix is set up like this, but on PowerBI: 



You can expand 'MMM tasks' and look at all the unique MMM ID #'s/parent tasks. When you click on any of the ID #'s, for example 'MMM3', you would get a list of tasks associated with that string (These data points are found in the duplicated data set that match the original dataset:




I am trying to obtain this last column on my matrix: 




I need to look at that same list of tasks in my measure/calculated column that show up when you click on each MMM task. It then brings in the first task ID # in that list. Since most dates are the same, I just need to bring in the first task that shows up when you drill-through to the data. I've tried a few ways of doing it (Lookup value, Firstnonblank), but I seem to be doing something wrong. How do I go about showing that in my matrix properly? 




Super User
Super User

It then brings in the first task ID # in that list. 

Power BI has no concept of that. You need to provide guidance on what "first" means. is it done by sorting the ID # field alphabetically?


On an unrelated note - are you aware of the PATH functions?

I have it sorted by the finish date of the task, but I'm not sure how powerbi sorts it after that if there are multiple tasks with the same finish date

That is exactly the type of ambiguity you want to avoid.  Best to bring your own index column.

Helpful resources

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors