Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
PPsi
Frequent Visitor

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

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

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!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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