cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

LOOKUPVALUE And Return Multiple Values Horizontally

Hello

Is it possible to stack mutiple values horizontally using LOOKUPVALUE in powerbi. i saw its possible in excel using INDEX, so i tried with SUBSTITUTEWITHINDEX, with no luck

following is the link for excel solution

https://www.extendoffice.com/documents/excel/2623-excel-vlookup-return-multiple-values-horizontally.... 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: LOOKUPVALUE And Return Multiple Values Horizontally

OK, if you do not need them to be in individual columns, you can do this:

 

Measure = CONCATENATEX(DISTINCT(FILTER('Table',[Status]="verified")),[Code],",")

 

See attached PBIX file. 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: LOOKUPVALUE And Return Multiple Values Horizontally

Are you trying to do this in a table, a visual or ? Can you provide more information? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: LOOKUPVALUE And Return Multiple Values Horizontally

Hello Greg

Sorry that i structured it badly. I will try to explain in a better way

Below is the table A

db new.png

So table A should check for Criteria in Table B (below)

db2.png

If the criteria matches and the Status is "Verified", it shoud give me data correspoding to marching criteria horizontally (As highlighted in Table A) if status is "not verified" 0 or NA

 

My idea was to use use LOOKUPVALE, but i dont know how to use in combination (i am a newbee with powerbi)

Thanks in advance

Highlighted
Super User IV
Super User IV

Re: LOOKUPVALUE And Return Multiple Values Horizontally

OK, @bishnu I'm trying to stick with you here. Can you provide me your expected results from the example data? From what I understand, you for each Criteria in Table A, you want to make sure that the values that appear in Doc1, Doc2, and Doc3 all appear in Table B with a Status of verified, is that close?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: LOOKUPVALUE And Return Multiple Values Horizontally

Hello greg

Thank you

The expected results are highlighted in green for Doc1, Doc2, and Doc3 in table A. I didnt know how to do it, so i entered it manually so that i what i wants as my result.

So basically the table B status will change, say like "N\A, Not verified, or Verified" but obviously final result will be "Verified".

I am trying to create this table A in powerbi so that i can keep a track of "N\A, Not verified, or Verified" doc's(documents) corresponding to each criteria.

i didnt understand what you meant by close!

 

Regards

B

Highlighted
Super User IV
Super User IV

Re: LOOKUPVALUE And Return Multiple Values Horizontally

OK, do you have an Index column for your source data table or can you add one?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: LOOKUPVALUE And Return Multiple Values Horizontally

Hello Greg

As if now i didnt add index column for both Table A or source data Table B. But i can if its needed

Highlighted
Super User IV
Super User IV

Re: LOOKUPVALUE And Return Multiple Values Horizontally

OK, if you do not need them to be in individual columns, you can do this:

 

Measure = CONCATENATEX(DISTINCT(FILTER('Table',[Status]="verified")),[Code],",")

 

See attached PBIX file. 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors