DAX formula for Vlookup to another table

0
votes

I'm using a form to capture data and import to a table. In this form, there is a multi-select option. When the table captures the data, it looks like this.

Table1

  • 1st item - Value1
  • 2nd item - Value1,Value3
  • 3rd item - Value2,Value3
  • 4th item - Value1,Value2,Value3
  • 5th item - Value2
  • 6th item - Value3

When I create a PowerBI filter on these data from Table1, it looks like this...

  • Value1
  • Value1,Value2,Value3
  • Value1,Value3
  • Value2,Value3
  • Value2
  • Value3

I want to create a VLOOKUP formula of sorts that will find all the instances of a value. Filter in PowerBI would ideally look like this:

  • Value1
  • Value2
  • Value3

My first inclination would be to create Table2 with the values set up

  • Value1
  • Value2
  • Value3

Now I just need the formula to search Table1 with a filter based upon Table2. How do I set up my formula to do this?

1
powerbidaxvlookuppowerbi-desktoppowerbi-datasource

1 Answers

0
votes

Option-1: You can split your item column using comma separator and create one row per item. Now create the Filter visual using the item column and you will get related rows always as per selected item in the filter visual. You do not need Table2 in this case.

Option-2: As you said, create the Table2 with value - Value1, Value2 and Value3. Now create a Measure in table one where you will return 1 or 0 based on selected item in the filter visual. Finally apply filter using the Measure = 1 or something like this. You can use IN operator to compare values between Filter and Table1.