Is it possible for me to build a matrix from the value of another?
What I'm trying to do: Below displays a mockup of a 3rd matrix (bottom right) of what I would like to do, where the Destination Channel value of the top matrix drives the filtering of the 3rd. The second caveat is that a Source could have multiple Destination Channels. So, I'm uncertain how I may do this and looking for some thoughts, insights? I haven't worked with any DAX yet, but I wasn't sure if it was possible, and if so, would it be through DAX, creation of a Dimension table and relationship, combination, or what exactly. Sorry for the crudenes of my example, but my data is confidential which is why these say "dummy", like me. 🙂
What I have done: I figure someone has tried this and I have searched for articles or tutorials, but most were about creating a matrix, not linking them so-to-speak in the manner I'm asking. The closest I arrived was the slicer you see sorting the two matrices (top and bottom) by a single value, but not how to drive another matrix based on the value of another. An example may be out there, I just hadn't come across it yet.
The Data: I have two Fact tables. The 1st Fact table provides the Source to Destination relationship you see in the top matrix, the 2nd Fact Table provides all the intricate details for Source and Destination I'm trying to capture in my mockup (bottom two matrix). I had to create a de-duped Source Dimension table for the Source and I have a de-duped one for the Destination, but I'm not sure how to incorporate the Destination in this scenario... if I can at all.
