Help with Joining multiple tables in Power BI

[SarahHope]

Hello,  I need some help with a Join in Power BI.

I wrote SQL to include all the columns I want in my power BI report, just to prove to myself that I was joining tables correctly.  The SQL is as follows:
SELECT Tck_ID, Tck_Title, AV_Value, Task_ID, User_FullName,AC.ATTC_Name AS ChangeType
FROM AttributeChoices AC
JOIN Attributes A ON AC.ATTC_ATTID = A.Att_ID
JOIN AttributeValues AV ON A.Att_ID = AV.AV_ATTID
JOIN Tickets T ON AV.AV_ItemID = T.Tck_ID
JOIN TicketTasks TT ON T.Tck_ID = TT.Task_TicketID
JOIN Users U ON T.Tck_CreatedByUID = U.GUID
WHERE AV.AV_Value = AC.ATTC_ID AND AV_ATTID = '3884';   --just to reduce size of data output

Here are the relationships in PowerBI.  I've tried some various combinations.

I can get all the fields onto the dashboard but not in the same report.
If I add AV_Value to the top report, I get "cannot be displayed"
If I choose "don't summarize" for Task_ID on the bottom visual, I get "cannot be displayed"

So currently, the situation is this:
I can display on the top visual: Ticket, Task, User on the top visual.
On the bottom I can display Ticket, Attributes, AttributeValues, AttributeChoices, Users, but only a COUNT of TicketTask.Task_ID

I've spent an inordinate amount of time on this, so I was hoping that someone here might see my issue quickly due to experience.

Thanks, 

Sarah

[SarahHope]

Wilson,

Just to follow up - Your advice helped me to consider some other options.  What I ended up doing is importing tables using SQL.  So one that had the fields I wanted and one attribute. Then I pulled in several other tables containing just the ticket ID and the specific attribute value.  From there, it was easy to join them.  

Thanks!

Sarah

[Wilson_]

Awesome, glad you figured something out Sarah. Thanks for the update! 😄

[Wilson_]

Hi Sarah,

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂

[SarahHope]

Hello Wilson,

Thanks - I saved the pbix, noticed that I couldn't attach here, so tried to put it in OneDrive and it won't sync.  I put it in Google Drive.   I hope this works:  https://drive.google.com/file/d/1FRX0kSxToVluVsjoztP3ObJx5trfzOrn/view?usp=sharing
Please let me know if I should do this differently.   Thanks! ~Sarah

[Wilson_]

Hey Sarah,

Looking through your data model, I can at least answer the broader question of why it's not working the way you're expecting. Tickets has a one-to-many relationship with both TicketTasks and AttributeValues and you're trying to pull in individual values from both tables on the many side of the relationship. This is perfectly fine in SQL because SQL's default behaviour will just be to essentially cross join between TicketTasks and AttributeValues. Hypothetically, if a ticket id has three associated tasks and three associated attribute values, you'll see nine records in the SQL query result for that ticket id.

However, Power BI doesn't work that way. There's no relationship between TicketTasks and AttributeValues so Power BI doesn't know what to do, if you aren't returning aggregated data from the two tables on the many side of the one-to-many relationship.

I think your issue is ultimately a data modeling issue. I suspect in the hypothetical where a ticket id has three associated tasks and three associated attribute values, what you actually want to see is not the nine records anyway?

----------------------------------
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?)

[SarahHope]

Good Morning Wilson,

Thanks for taking time to look at my stuff.  Your explaination makes sense.  What I do want is all of the tasks associated with a ticket.  In power BI I want the values associated with a couple of the attributes.  So in your example of a ticket with three tasks and three attributes, I would expect three records.

Despite that I understand the explanation, I don't know how to fix it as there are no fields in TicketTasks that are directly related to AttributeValues.

In the end, I want a result like this (I've omited fields because they are simple to grab from tickets or tasks):

Tck_ID

Task_Title

User_FullName

ATTC_Name (associated with Attribute Value when the Attribute = 3884) "Change Type"

ATTC_Name (Assocaited with Attribute Value when the Attribute = 3713) "Downtime"

So, I do expect mutliple ticket numbers on my report to equal the number of tasks.

The way that the attribute tables work is:

• TicketTasks - tickets themselves do not have date/time. There could be several tasks scheduled for different date/times assoociated with each ticket.
• Attribute = The ID / name of the form fields that users are presented with (ex: change type)
• AttributeChoices = The ID / names of the options for each attribute (ex: Normal, Emergency, etc.
• AttributeValues = The Value that the user selected - this is associated with tickets. 
• AV_ItemID = Tck_ID

Is there a way to make it work?

[Wilson_ · Accepted solution]

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?)

[SarahHope]

This does help and I will need some time to fiddle with it to see if it works. (Advaced Beginner Leve here)   I like the idea and I know how to add these columns (I think).  I'll give it a shot.  Thanks!

[SarahHope]

Apologies - this app stripped my nice table, but that was supposed to be four neat columns.