Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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
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.