cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 I
Super User I

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 I
Super User I

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 I
Super User I

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. 

Super User II
Super User II

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. 


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

Proud to be a Super User!


MCSA: BI Reporting
Super User I
Super User I

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 I
Super User I

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 I
Super User I

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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors