I have a data set utilizing a hierarchy type order. as per below screen shot
In the data set I am trying to design on a as high level as possible to minimize the amount of input needed.
However, for example I have an ID for product of 0-1 it means this should apply to all levels below where ID starts with 0-1, so 0-1-1, 0-1-0 etc.
So it essentially utilize a parent - child type logic.
I have tried unpivot it however then starts getting troubles with the relationships due to it becoming a many to many relationship (not sure if this is the way though). I did try to do it through a DAX but couldn't get the logic right.
In the end what I am trying to get to is tthis
1. set a mandatory filter on Product Hierarchy Level 2 / Level 22 (desciprtion) OK
2. for certain instances this will leave level 4 / Level 44 (blank) - this is right but means it applies to all level4 /44 possibilities as described above due to P to C relationship
3. if level4 is (blank) I want this to remain unseen by user ( so from a user POV you can not choose it)
4. However, if another level 4 / 44 is then chosen it will append the data and not replace it.
so I think of it in this way, you can do this with allowing multi selection and then do CTRL + Selection (standard). essentially I want it to behave the same but without showing the blanks and having to do CTRL + selection.
To allow a much smoother interaction and removing "unessary information" and steps for users.
is this possible? any ideas? I am running out of ideas to implement it.
see below link to report
Hope this explanation makes sense.
thanks for any help in advance