Forum Discussion
Help with Table relationships
Hello,
I am currently working on a project that aims to visualize ticketing data for our firm (tickets per 1000 employees)
I have two datasets - Ticketing data and headcount data
Ticketing data has a Region Field and a Date Field
Headcount Data has a Region Field and a Date field
I created a unique regions table, and related both tables to it. That relationship works and I can see the # of tickets/1000 employees by region.
however, I want to show this data over time by quarter.
So I created a calendar table, and tried to relate both my datasets to it, but I am getting an ambiguity error. I am fairly new to doing this in powerBI. I can accomplish it relatively easily in Tableau, so I am just wondering where I am going wrong.
Any help would be appreciated!!!
Hi all, thanks for the quick reply, I'll add more.
Hi Timmo1016 ,
A situation like this?
Please change the data type of the 'Date' column to Date
Best Regards,
Wenbin Zhou
9 Replies
Please provide more details.
What's the error?
What does your relationship diagram look like?
Sample data?
Thank you for your reply. See my reply to Kedar above
Ensure you have relationships set up with both the Ticketing and Headcount tables based on the Date fields with calendar table.
Set one of the relationships to be inactive (you can do this by selecting the relationship and toggling the "Active" setting).
TicketsPer1000Employees =
CALCULATE(
SUM(Ticketing[TicketCount]),
USERELATIONSHIP(Ticketing[Date], Calendar[Date])
) / (SUM(Headcount[EmployeeCount]) / 1000)
When creating your visual, ensure you're using the calendar table's fields (like Quarter) to plot your data over time.Ensure the date fields in both datasets and the calendar table are of the same data type (Date).
If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pandeThank you for your response. I realized i don't really need the Region Relationship so this is much more simple now but I am still having trouble.
What I have now is 3 data tables, all of which have a relevant date column. I tried to create a calendar table to relate to each of them like below (key fields highlighted)
But the visualizaiton seems to put everything as blank date if I use the date hierarchy from the calendar table i created.
Hi all, thanks for the quick reply, I'll add more.
Hi Timmo1016 ,
A situation like this?
Please change the data type of the 'Date' column to Date
Best Regards,
Wenbin Zhou
It looks like there is a time value in the transaction tables. Can I get rid of them somehow?
Timmo1016 also you don't need to use the userelationship function because all the relationships are active, userelationship is only used to make inactive relationships to active. If interested to know more about this here USERELATIONSHIP function (DAX) - DAX | Microsoft Learn
