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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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