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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jabbajuice08
Frequent Visitor

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

jabbajuice08_0-1670009267901.png

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: 

jabbajuice08_2-1670009451032.png

 

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:

 

jabbajuice08_3-1670009486524.png

 

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

 

jabbajuice08_4-1670009540107.png

 

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? 

 

 

 

3 REPLIES 3
lbendlin
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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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