The more I think about this, the more intriguing it gets. I think there’s a way to have our cake and eat it too on this. What if the ID used in Customer is 1 to (N + 1), where N is 1500 or so, and the +1 is the additional row where a separate ID is assigned to the named “composite” customer? Then in the fact table, we have two key fields – CustID and CustSpecID, one connected via active relationship and the other connected via inactive relationship to CustIDN+1. This maintains referential integrity and allow us to dynamically roll or unroll the “Other” category, depending which relationship gets switched on.
Makes the DAX a bit more complicated than the PQ rollup approach I proposed above, but not overly so, and I think gives you a lot of dynamic flexibility in the analysis. Perhaps it might be worth generating a dummy dataset using one of these tools/techniques that we could experiment with?
Thoughts?
- Brian