Help with Joining multiple tables in Power BI

Sarah,

I think I understand your data better now, thanks for elaborating. Given your additional information, let me explain your original problem a different way:

If a ticket (ex: #1) has three tasks (ex: A, B, C) and three attributes (ex: Change Type = X, Service Line = Y, BackoutPlan = Z) and you expect three lines, I understand intuitively that you want to see the task ID repeated on each line, with each each of the three different tasks on one of the three lines. However, because there are also three attributes associated with your ticket, how do I know which of the three attributes to show on the three lines? It seems your answer is all of them (which makes sense!). As your data model is currently constructed, this is impossible to know or do.

Side note: What SQL would return for this hypothetical ticket #1 above is this. That's not what you're looking for either.

Ultimately this does come down to a data modeling issue. It sounds like attributes are part of the ticket entity and attributes exist at the ticket granularity, so to speak. Put another way, only tickets have attributes. Therefore, they should be denormalized into the same table. Since you have 31 different attributes, what I would end up doing is adding 31 columns in the ticket table with the attribute values for that ticket (and probably removing aggregated values like estimated ticket time from the ticket table, since it looks like they're stored at the ticket task detail level too).

Essentially, what this does is turn the ticket table into a dimension table, while leaving TicketTasks as your one fact table.

Hopefully that makes sense!

P.S.: I have other thoughts on the data model in general, but they don't pertain to this issue so I'll keep them to myself. 😂

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)