cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Create Table Relationship

Hi,

 

I am trying to join the following three tables without success. Can someone please help?

 

armchairexpert_0-1601480438259.png

 

Currently I have joined the Date table to Calls and Agent Times tables using Call Date. Agent Times has single records per day per agent. Calls table has multiple records; details of many calls per day per agent.

 

I use Date, Month, Week Number and Year as filters from the Date table. I use Agent, Call Direction, Call Queue, Call Status, Call Type and Company as filters from the Calls table. All of this in a single report. 

 

I need to somehow join Agent in Calls and Agent Times tables. Cannot join as it is as it gives a many to many error. I need this for a new report for Agent Times where I need to use all these filters but not sure how to connect Calls and Agent Times table.

 

Many thanks in advance.

1 ACCEPTED SOLUTION

Do the agents belong to a company? Is that an attribute you could move up to the Agents table? What are you trying to calculate off the Agent Times table?

View solution in original post

9 REPLIES 9
Super User II
Super User II

@armchairexpert 

You will need an Agent table that sits at the same level as the date table.  It should contain the unique list of agents and that you can connect to the 'Agent Time' and Calls table to use to filter the model by Agent.

@jdbuchanan71,

 

It doesn't work. I picked the wrong date to filter earlier. I tried to filter by Company where all the companies had records for a day but it shows all the agents for all the companies.

Do the agents belong to a company? Is that an attribute you could move up to the Agents table? What are you trying to calculate off the Agent Times table?

View solution in original post

@jdbuchanan71,

 

I found why I was getting Many to Many error with Agent and Company. Same Agent name was found in more than one company. After sorting that out I moved the Company to the Agent table as you suggested and built the relationship. That is working now.

 

armchairexpert_0-1601549325314.png

 

Thanks for your help.

@jdbuchanan71,

 

All the agents have a company; it is like different branches. Using the company I filter and show the Total Calls, Total Duration, etc. I moved the Company to the Agent table and that created different problems. I think many to many problems. Then again, it is not just the company, as I mentioned on my original post, I will have many filters and I will have to move all of them up. That didn't work.

Could you perhaps share your .pbix file?  What are you trying to calculate off the Agent Times table?

Sorry, too much sensitive information and organisation data in pbix.

@jdbuchanan71,

 

Apologies. It let me filter but had issues with something else. Let me re-create the problem.

 

 

@jdbuchanan71,

 

Already tried that and that didn't work. My new report is based on the Agent Times table and it doesn't let me filter using anything on the Calls table. For example, it doesn't let me filter by Company.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors