Filter on date from other table

[Anonymous]

My dataset contains a table with support tickets: Ticket. This table contains a date-field: CreatedDate. Each Ticket has a corresponding Project (Relationship: Ticket.ProjectId-Project.ProjectId). A project can be completed. In this case Project.CompletedDate gets filled in.

I have made a card visual showing the total amount of Tickets created (Using a date table). I have used a slicer for this (relative date slicer, say: last 7 days). Now, I want to create a new card displaying the total amount of completed Tickets within these 7 days (count tickets joining on ProjectId where Project.Completeddate = within 7 days). I want to use that same slicer for that.

I can't really figure out how to achieve this, since I am fairly new to Power Bi.

I hope I've made my problem clear...

Thanks in advance

[parry2k]

@Anonymous create a relationship between date table and completed date, it will be an inactive relationship because you already have a relationship on create date and inactive relationship is ok, add the following measure for completed tasks count

Completed Count = 
CALCUALATE ( COUNTROWS ( TicketTable ), 
USERELATIONSHIP ( TicketTable[CompletedDate], DateTable[Date] ), 
TicketTable[CompletedDate] <> BLANK()
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I Don't really understand how this works and my own DAX formula doesn't XD.

Thanks a lot!

And I was wondering: If I wanted to create a card showing me all tickets that have been created (Ticket.CreatedDate) AND completed (Project.CompletedDate) within these 7 days, how would I do that? Because then I would need to use both relationships in one formula it seems...

[v-yiruan-msft]

Hi @Anonymous ,

Could you please provide some sample data(exclude sensitive data) in table Ticket and Project? Then we can provide you the measures or sample pbix file which suitable for your scenario. 

In addition, you can refer the contents in the following links to achieve it.

POWER BI AND DAX – USERELATIONSHIP TO WORK WITH MULTIPLE DATES 

Calculate Transactions In Last N Days in Power BI Using DAX

Measure for count of last 7 days

Best Regards

Rena