Use datetable with multiple dates in one table

[Anonymous]

I am quite new to Power Bi, and I'm just getting started to understand the basics of it.

Now, I came across a problem. I have got a data set with a table with customer support tickets containing multiple date fields (Ticket.CreatedDate and Ticket.ClosedDate). I want to make a graph where I can see the total amount of support tickets per week. To make this work, I discovered date tables in Power Bi. I created a date table containing date, using CALENDARAUTO(), weeknumber, year, month etc. etc.

But having a reationship between Ticket.CreatedDate and Calendar.Date, makes it unable to use Ticket.ClosedDate in another graph.

I have read something about USERELATIONSHIP in DAX, but I don't know how to make this work since I don't really need to calculate something. I only want to use year and weeknumber for zooming in (or 'filtering' or 'slicing') on my graphs.

How can I achieve what I am looking for?

Thanks in advance,

Werner

[amitchandak · Accepted solution]

@Anonymous , find the use of use relation here

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Try like

Ticket Created = CALCULATE(COUNT(Ticket[Ticket Id ]),USERELATIONSHIP(Ticket[CreatedDate ],'Date'[Date]) )

Ticket Closed = CALCULATE(COUNT(Ticket[Ticket Id ]),USERELATIONSHIP(Ticket[ClosedDate],'Date'[Date]))

Join both dates with the date calendar. Use relation is optional with active join.

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

[Anonymous]

That's it!

The problem I was facing actually had nothing to do with the relationships.

My dataset uses datetime (dd/mm/yyyy hh:mm:ss) and the calendar I made obviously uses date (dd/mm/yyyy). I thought I formatted the dataset correctly in Power BI, but apparently BI is still unable to handle these (sort of) 'different' datatypes, although they look exactly the same. Had to change the dates in PowerQuery itself to make it work.

Anyways, Thanks a lot!

[harshnathani]

Hi @Anonymous .

See if this helps

https://www.youtube.com/watch?v=sONvctPlplY

https://www.youtube.com/watch?v=r8e_bRJ07N0

https://www.youtube.com/watch?v=u8_aeLmXbVk

https://dax.guide/userelationship/

Regards,

Harsh Nathani