I’m digging into this one now, but first wanted to give a big thumbs up to the great work you’ve done reworking your data model. When we worked on the totals/filter conditions issues back in Dec, this model was chockablock with inactive and bidirectional relationships, making it pretty hard to follow. If you don’t mind, I wanted to offer two suggestions, which I think will simplify/clarify it even further:
- Use TREATAS to get rid of the two bridge tables. Will make your DAX slightly more complicated, but IMO that is more than offset by the simplicity gains in the data model.
- Display the model in Collie format, with the dimension tables all at the top level, so visually all the relationships/filters flow “downhill”:
@sam.mckay promotes this as a best practice, and I completely agree that it makes any data model clearer and more intuitive (I find it also really helps me better visualize what DAX is doing).
Will get back to you later today on the issue at hand, but just wanted to let you know that I think the overall model is looking good.