Help with Table relationships

[Timmo1016]

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!!!

[parry2k]

@Timmo1016 there are couple of ways, preference would be change the data type to date of this column in the PQ (Transform data) but if for some reason you also need the datetime column, then extract date from this column, and add a new column in PQ

Date.From([Existing Date Time Column])

[parry2k]

@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

[parry2k]

@Timmo1016 only reason it would happen if the values in the date table doesn't match the values in the transactional table. Can you make sure there is no time value in the transaction table? Check the data  preview in the transform data (power query).

[Timmo1016]

It looks like there is a time value in the transaction tables. Can I get rid of them somehow?

[Kedar_Pande]

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-pande

[Timmo1016]

Thank 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.

[Anonymous · Accepted solution]

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

[HotChilli]

Please provide more details.

What's the error?

What does your relationship diagram look like?

Sample data?

[Timmo1016]

Thank you for your reply. See my reply to Kedar above