Creating relationship problem in PowerBi

[Angiee]

Hello, I have difficulties on creating relationship in powerbi.

I have 2 fact tables F_SCC_Ticket_and_Inquiries and F_Escalations - both tables contain Ticket ID field. However, I would like to get the deal value from F_SCC_Ticket_and_Inquiries table, but passing by the Opportunity ID (which i do not have in F_Escalations table), not the Ticket ID, because what happens is that there could be Ticket ID in the F_Escalations table that doesn't have value in the F_SCC_Ticket_and_Inquiries table, but the Opportunity ID for sure has value in another Ticket ID.

I have tried many to many relationship between both tables based on Ticket ID, but still if there is no deal value for the particular Ticket ID shows blank - but yet there is a value based on opportunity ID that appears in another Ticket ID. 

I hope I explained clear and thanks in advance!

[Angiee]

Hello Gökberk Uzuntaş,

Appreciate your time on this.

I have disabled the many to many relationship, because it was not doing what i wanted and tried different approaches. The deal value is a New and Existing Sales SUM measure i have in F_SCC_Ticket_and_Inquiries table. The idea is to show the business impact of the escalations by getting the deal value from F_SCC_Ticket_and_Inquiries table throught the Opportunity ID. The point is that in F_SCC_Ticket_and_Inquiries table i can have many different ticket ID's with same Opportunity ID, but the value will be in only one Ticket ID and same Ticket ID is not neccesarly in F_Escalation table, but the deal value for the same Opportunity ID will appear in another Ticket ID F_SCC_Ticket_and_Inquiries table.

In the example below with Many-to-Many active relationship that is what happens:
I have in F_Escalation table Ticket ID's - 2 and 5. I have same in F_SCC_Ticket_and_Inquiries, but there is no Deal value for them. However I would like to see the Deal value which appears in Ticket ID 1 and 7, because the Opportunity ID in Ticket ID 5 and 1 is the same and in 2 and 7 is again same. 

[uzuntasgokberk]

Hello @Angiee ,

Your many to many relationship is not active. You might used dax formula USERELATIONSHIP to calculate or active it. However, I would recomment create detail Dim tables to implement glaxy schema for your case. I'm sure you rename more suitable for your DimTable(Mine is just examples) such as DimTicket table that includes Ticket ID and relative columns, DimEscalatio table that includes Escalation ID and relative columns, DimOppurnity table that includes Oppurtunity ID and relative columns and more...  That is best practice for data modelling.

But what I don't understand is you want to get deal value(I couldn't see in the image. You mean Count row etc?) where is F_SCC_Ticket_and_Inquiries and includes Oppurnity ID. From F_Escalations table what would you like to test?Could you explain a bit more? I'm a bit confused that you'd like to calculate.

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |