Relationship and hierarchy

[grazib]

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

[grazib]

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

[TotunG]

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.