Reply
PPsi
Frequent Visitor
Partially syndicated - Outbound

Connect 2 tables with hierarchical "Category" values

I have 2 tables: Families and Parameters. Each family and each parameter has an assigned category. The categories consist of 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 lower subcategories. 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.

How can I visualise it?

I mean to create 2 separate visuals (one common would be nod bad as well): one with families, the second one with parameters. If I chose one familie, I want to se all associated parameters.
I tried with hierarchies (split category into 3 separate columns and set a hierarchy) but I had no idea hot to connect then the tables in the model.

pbix 

1 REPLY 1
PPsi
Frequent Visitor

Syndicated - Outbound

I wrote this in Python, maybe someone can translate it into Power BI?

 

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

# Family dataframe
fam_data = {'Family': [f'Family{i}' for i in range(1, 92)],
'Category': [num for num in range(400, 500) if num < 401 or num > 409]}
df_fam = pd.DataFrame(fam_data)

# Parameter dataframe
par_data = {'Parameter': [f'Parameter{i}' for i in range(1, 92)],
'Category': [num for num in range(400, 500) if num < 401 or num > 409]}
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)

number = random.randint(410, 500)
print(number)
print(my_function(number))
 
497
Parametr Category
0 Parametr1 400
81 Parametr82 490
88 Parametr89 497
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)