Search and map values if contains from another table

[adityavighne]

Hi I need help regarding

I have

table 1

Category	Type
Category 1	Type1
Category 2	Type2
Category 3	Type3

table 2

Country	Type
USA	Type 1, Type 3
Japan	Type 2
India	Type 2, Type 1, Type 3

now I want to filter table 2 based on table 1 category. if type contain in table 2 and show countries as output

if I filter Category 1 then output will be
USA

India

Thanks.

[bhanu_gautam · Accepted solution]

@adityavighne 

Load the Data: Load both Table 1 and Table 2 into Power BI.

Ensure there is a relationship between the two tables based on the Type column. If the Type column in Table 2 contains multiple types separated by commas, you will need to split these into separate rows.

Split the Type Column in Table 2:

Go to the Power Query Editor.
Select the Type column in Table 2.
Use the "Split Column" feature to split by delimiter (comma in this case).
This will create multiple columns for each type. You can then unpivot these columns to create a row for each type.

Unpivot the Split Columns:

Select the newly created columns from the split operation.
Use the "Unpivot Columns" feature to transform these columns into rows.

Create a Filter:

Create a slicer visual in Power BI and use the Category column from Table 1.
This slicer will allow you to filter based on the selected category.

Create a measure or calculated column to filter Table 2 based on the selected category from Table 1.

DAX
SelectedCountries =
VAR SelectedCategory = SELECTEDVALUE('Table 1'[Category])
VAR SelectedType = CALCULATE(VALUES('Table 1'[Type]), 'Table 1'[Category] = SelectedCategory)
RETURN
CALCULATETABLE(
VALUES('Table 2'[Country]),
FILTER(
'Table 2',
CONTAINSSTRING('Table 2'[Type], SelectedType)
)
)

[v-aatheeque]

Hi @adityavighne ,

If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!