For Table1[Column] find multiple matching values from Table2[Column]

[PPsi]

Hi,

I have 2 tables, let us say "Product" and "Attribute" both contain "Category-NR", which is 5-digit numeric value. It's kind of hierarchy - the first digit is main category, the next are subcategories. If an attribute can't be subcategorized, we use 0 on the right place(s): for example 45600 means that the attribute is valid for all products with Cat-Nrs 45600-45699. 

All unique categories are listed in separate table and related with my 2 tables.

My quest is: to put 2 tables in a dashboard, and filter them so, that when I chose one product, the second table shows only attributes related with category of the product.

BUT: not only with the subcategory (for example 45689) but also attributes with no subcategories, also for our example: 45680, 45600, 45000, 40000.
Any idea how to do it, will be apperciated

[PPsi]

Maybe such a sample to show the rule. By chosen highlighted row from Product, the highlighted rows from Attributes should be filtered:

[Ahmedx]

explain your logic again
why do you need to should 43100 and not 43128?

and if we select 40027 then what should we select?

[PPsi]

Par2Fam 

Hello Ahmedx, thank you for your answer. You're right. My translation was not very clear, and the example I provided was incorrectly generated (0 can't occure before 1-9 values). Unfortunately, this is a problem that is probably easier to solve than to explain, and I can't share too much company data. I tried to extract the necessary data and am providing it in a .pibx file.

I have 2 tables: Families and Parameters. Each family and each parameter has an assigned category. The categories consist of (for simplicity) 3 digits from 1-9, with the possibility of a 0 in position 2, or in positions 2 and 3 in the case of no subcategory.

The task is to assign parameters to families. And the biggest problem: the category consists of 3 levels. Not all parameters reach the lowest subcategory. This means that they apply to all subcategories. For example, a parameter with a category of 400 (main category 4, 2 unspecified subcategories) applies to all families in the main category 4, i.e., from 411 to 499. But a parameter with a category of 438 applies only to families in the same category. And vice versa: selecting a family with a category of 357, I should get all parameters from the following categories: 357, 350, 300. Because if a parameter has a category of 350, it means it should be assigned to all families with a category of 35x.

[PPsi]

To understand it even better what I need, in python this filter function would look so like:

def round_numbers(num):
return num, num // 10 * 10, num // 100 * 100
import pandas as pd

# Family dataframe
fam_data = {'Rodzina': [f'Rodzina{i}' for i in range(1, 101)],
'Category': list(range(400, 500))}
df_fam = pd.DataFrame(fam_data)

# Parameter dataframe
par_data = {'Parametr': [f'Parametr{i}' for i in range(1, 101)],
'Category': list(range(400, 500))}
df_par = pd.DataFrame(par_data)

def round_numbers(num):
return num, num // 10 * 10, num // 100 * 100

def my_function (fam_category):
cat, cat_10, cat_100 = round_numbers(fam_category)
filtered_params = df_par[df_par['Category'].isin([cat, cat_10, cat_100])]
return(filtered_params)

print(my_function(495))
Parametr Category
0 Parametr1 400
90 Parametr91 490
95 Parametr96 495

Maybe this helps?

[Ahmedx]

Share sample pbix file to help you.