Matrix Category and Subcategory

[Anonymous]

I need to setup a matrix to show categories of items. Some of the categories have subcategories, and some do not. My dataset has a field for "Category" and a field for "Subcategory". Right now I have Subcategory defaulting to null if there isn't one. 

Currently it looks like this

However, I don't want it to expand to another level if there isn't a subcategory, so it'd look more like this

[ChrisMendoza · Accepted solution]

Have you taken a look at the techniques described at https://www.daxpatterns.com/parent-child-hierarchies/# ?

[Anonymous]

@ChrisMendoza Oh wow that is great! Thanks so much. One question though - how does the Entity table join to the StrategyPlan table? I keep getting a circular reference error when I try to join on Category to Level2 and Subcategory to Level3

[ChrisMendoza]

@Anonymous - Hard to tell without knowing your data model; honestly even if I saw it I'm not sure I'd be able to provide a sufficient response. In my model, I do remember that I did have a similar issue though. If I recall, I had to create 'bridge' tables and when creating the relationship it actually mattered which started the connection and which ended the connection; hopefully that makes sense and helps you.