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

lookup value bytext in another table

dear forum 

I'm seeking to return 'Svar' number by a text measure in another table. 

The data looks like this: power bi forum 2.JPG
power bi forum.JPG

I want to find values in response collum when the the textvalue is "Omsætning i t.kr." in the 'finansiel række' collum in the question table.

  preferably it can be made as a measure. 

been trying the lookupfuction: 

Omsætning = LOOKUPVALUE(Response[Svar];Question[Finansiel række];"Omsætning i t.kr.")

does not display anything though. Perhabs because there are duplicates?


Hope someone can help

best regards severin

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: lookup value bytext in another table

@Severindj Please try this as a measure

 

TotalResponses = CALCULATE(SUM(MultiLkpTest[Response]),FILTER(ALL(MultiLkpTest),MultiLkpTest[Text]="Test1"))

image.pngSample Dataimage.pngOutput



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

Super User
Super User

Re: lookup value bytext in another table

@Severindj 

 

TotalResponses = CALCULATE(SUM(Response[ResponseValue]),FILTER(ALL(Question),Question[QuestionName]="Test1"))

image.pngQuestionimage.pngResponseimage.pngRelationshipimage.pngOutput



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

9 REPLIES 9
Super User
Super User

Re: lookup value bytext in another table

@Severindj  Are you trying as a calculated column or measure ? Also, if there are multiple responses for that particular question then what you want to do ?



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Severindj Frequent Visitor
Frequent Visitor

Re: lookup value bytext in another table

Dear @PattemManohar I'm trying as a measure. If there are multiple responses, which I know there is, I want to sum them all up. Hopefully it's understandable. 

edhans Super Contributor
Super Contributor

Re: lookup value bytext in another table

LOOKUPVALUE() will return an error if there are multiple records found where the the results column has different values. Your tables also need to be related.

 

See the documentation with an example. 

Super User
Super User

Re: lookup value bytext in another table

@Severindj Please try this as a measure

 

TotalResponses = CALCULATE(SUM(MultiLkpTest[Response]),FILTER(ALL(MultiLkpTest),MultiLkpTest[Text]="Test1"))

image.pngSample Dataimage.pngOutput



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

Severindj Frequent Visitor
Frequent Visitor

Re: lookup value bytext in another table

Thanks for reply. My tables are related by ID. There are multiple values in the 'finansiel række' collum  in the question table. 

Super User
Super User

Re: lookup value bytext in another table

Please post the sample test data and expected output to understand your requirement in detail


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Severindj Frequent Visitor
Frequent Visitor

Re: lookup value bytext in another table

Hopefully this makes sense. 

There are probably question dublicates, and therefore I need to sum them all up. they are related through id. 
power bi forum 3.JPG

Severindj Frequent Visitor
Frequent Visitor

Re: lookup value bytext in another table

It worked @PattemManohar ! Thank you so much, you're a star!

Super User
Super User

Re: lookup value bytext in another table

@Severindj 

 

TotalResponses = CALCULATE(SUM(Response[ResponseValue]),FILTER(ALL(Question),Question[QuestionName]="Test1"))

image.pngQuestionimage.pngResponseimage.pngRelationshipimage.pngOutput



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 86 members 1,400 guests
Please welcome our newest community members: