cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBiNoob
Frequent Visitor

power bi "Index Match"

Hi Power Users,

 

I have a excel formula that does what i need , but now to translate that to work in Power Bi has been a nightmare.

I tried to google for answers but no luck. Okay so hopefully my table and explenation will make sense. I have the fields (Id , Parent Id, Summary) , So the Id field wil always be unique, but the parent Id field can have multiple "numbers" that points to the Id field (see Id 3) , the summary field is description of Id and Parent Id (Where Id will be the Main info , and Parent Id the more detailed info. My question is I need to return the summary of the Id where the Parent Id matches the Id and not the summary value of the Parent Id "the more detailed part". My excel formula I use that works is  - =INDEX(C:C,MATCH(B:B,A:A,0))

Thanks in advance

 

IdParent IdSummaryWhat I Need
11AA
22BB
33CC
43C "and info of C"C
55EE
66FF
711G#N/A
812H#N/A
913I#N/A
1014J#N/A
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

How about a column formula such as:

Parent Summary = LOOKUPVALUE ( [Summary], Table1[Id], Table1[Parent Id] )

View solution in original post

Anonymous
Not applicable

@PowerBiNoob,

That's a column formula, not a measure formula.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

How about a column formula such as:

Parent Summary = LOOKUPVALUE ( [Summary], Table1[Id], Table1[Parent Id] )

If I do that it doesnt want to accept the last column value.

 

ParentSummary = LOOKUPVALUE (Top30ParentSum[summary], Top30ParentSum[Id], Top30ParentSum[parent.id])

"A single value for column 'parent.id' in table Top30parentsum' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation......"

 

Not sure if this would help

Anonymous
Not applicable

@PowerBiNoob,

That's a column formula, not a measure formula.

Sorry for the late reply , Thanks again for the solution , I tried the lookupvalue before but as a measure.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors