Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filter on date from other table

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

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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