March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
132 | |
90 | |
88 | |
64 | |
58 |
User | Count |
---|---|
203 | |
141 | |
107 | |
73 | |
70 |