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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
grazib
Regular Visitor

Relationship and hierarchy

Hi there,

I have 3 tables which hold categories and sub-categories as a hierarchy – not my model, but it came straight from mySQL like this:
- Category – categorID is the unique ID
- Sub-category – with categoryID as the FK
- Sub-sub-category with Sub-category ID as the FK

Then I have the main table ‘cases’ which have the following fields / values from the tables above:
- CategoryID
- Sub-CategoryID
- Sub-sub-category ID

I created a relationship between the 3 tables, so I can do some hierarchy matrix / report visual, and then created the relationship between each of those tables and ‘cases’. This won’t work, and will keep a relationship as ‘inactive’.

What’s the way around this?

Thank you,

Grazi

2 REPLIES 2
grazib
Regular Visitor

Thank you for  your reply. So I get the data as per the attached diagram straight from mySQL server. In the report, I'd like a matrix with the category - sub-category and sub-sub category and the distinct counts of the client IDs coming from that table. 

I'm not sure how to achieve this, as I can't make all those relationships active 😞
Thank you so much


image.png

TotunG
Resolver I
Resolver I

Seems like you need to simplify the hierarchy into one main Hierarchy table then link the 3 tables (which have the hierarchy values) to relate to this main table which you can then do your calculations from. 

 

Might be good to do a quick mock up of your PBI data if you can and post here if more help needed.

Helpful resources

Announcements
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!