How do I create a calculated column using DATEDIFF() on a filtered row from RELATEDTABLE column?

Question

I have three tables:

Tickets
-------
description
status(fk)
create_date


Status
--------
id
name
create_date


Ticket Status History
---------------------
id
status(fk)
create_date

Tickets have one-to-many relationship with Ticket Status History. I want to create a calculated column in Tickets such that displays the number of days between Ticket[create_date] and 'Ticket Status History'[create_date] where 'Ticket Status History'[status] = 1.

I'm using Power BI Desktop to generate a report from these tables. I have already extracted the data from the database into power BI. I've tried using DATEDIFF but I keep getting "cannot find create_date", the one in 'Ticket Status History'

ApprovedDays = 
  DATEDIFF(
  'Tickets'[create_date],
    MAX(
        FILTER(
        RELATEDTABLE('Ticket Status History'), 
        'Ticket Status History'[status] = 1),
        'Ticket Status History'[create_date]
      ),
      DAY
      )

I am new to DAX and can't seem to put together the code to produce this. Please help.

CR7SMS CR7SMS · Accepted Answer · 2019-08-22T19:22:27

The below formula should do the trick:

Approved Days = IF('Ticket Status History'[status]=1,DATEDIFF(RELATED(Tickets[Create_date]),'Ticket Status History'[create_date],DAY),0)

How do I create a calculated column using DATEDIFF() on a filtered row from RELATEDTABLE column?

1 Answers