Creating a calculated collumn with the use of filters

[JpTall]

I am trying to create a calculated column that captures the first appointment date that is an ‘install’ and is NOT cancelled.

I tried this formula:

First install date = CALCULATE(FIRSTDATE(APPOINTMENT[Appt_Date]),FILTER(APPOINTMENT,APPOINTMENT[App_Type_Code]="IN" && (APPOINTMENT[Cancelled]=BLANK()||APPOINTMENT[Cancelled]="N")))

IN = install

N= no

But I get the same date for each record in the entire column.

When I don’t use the filters and instead use this formula:

First appointment date = CALCULATE(FIRSTDATE(APPOINTMENT[Appt_Date])

However, it does a good job of getting the first appointment date for each record.

Is it possible to use a calculated column with filters to accurately get the first install date into my table? The formula with filters works with a measure but I need the column for my visuals and other calculations.

Thanks in advance for any help.

[JpTall]

Just a bit more information for what I'm trying to achieve.

The data:

Referral no.	Client no.	Appointment no.	Appt date	App_Type_Code	Cancelled
000001	001	0001	08/01/2019	AX	Y
000001	001	0002	21/01/2019	AX
000001	001	0003	02/02/2019	IN
000002	002	0004	15/01/2019	AX	N
000003	003	0005	15/01/2019	AX
000003	003	0006	28/01/2019	RE
000003	003	0007	05/02/2019	IN	Y
000003	003	0008	07/02/2019	IN	N
000003	003	0009	15/02/2019	IN
000004	002	0010	21/01/2019	AX
000004	002	0011	28/01/2019	IN

The end goal:

Referral no.	Client no.	Appointment no.	First Install Date
000001	001	0003	02/02/2019
000002	002	0004
000003	003	0008	07/02/2019
000004	002	0011	28/01/2019

[fhill]

*** These DAX values assume you only have 1 Date per Apt No line...  ***  and please excuse our reversed dates here in the states...

First Install Date = IF (CALCULATE( FIRSTDATE( Table1[Appt date]),  -- IF Statement to check for lines without an "IN" or "N" or "" or " " in Cancelled.  This is needed to produce a BLANK LINE with a DAX measure for any row NOT finding a match.

         FILTER(Table1, Table1[App_Type_Code] = "IN" && Table1[Cancelled] <> "Y")) = BLANK(), " "  -- Here's the Blank Line i'm using to 'fill' the blank row because DAX Measure typically exclude Blank results...

         , CALCULATE( FIRSTDATE( Table1[Appt date]), FILTER(Table1, Table1[App_Type_Code] = "IN" && Table1[Cancelled] <> "Y")))

Apt_No = IF (CALCULATE(FIRSTDATE( Table1[Appt date]), FILTER(Table1, Table1[App_Type_Code] = "IN" && Table1[Cancelled] <> "Y")) = BLANK(), MAX(Table1[Appointment no.]),  -- Check for No Good Installs, and use MAX Apt # instead as shown in your example...

CALCULATE(MAX(Table1[Appointment no.]), FILTER(Table1, Table1[App_Type_Code] = "IN" && Table1[Cancelled] <> "Y" && Table1[Appt date] = CALCULATE(FIRSTDATE( Table1[Appt date]), FILTER(Table1, Table1[App_Type_Code] = "IN" && Table1[Cancelled] <> "Y")))))   -- Else Find Apt # matching the First Install Date

[JpTall]

@fhill  Thank you for your response!

Did you use a measure or a calculated column? I still can't get this to work when I create a calculated column. And I have just noticed the value that is being produced for each record is incorrect (i.e. not an install). It is just the First Appt in the table, or MAX Appt no in the table. 

I have a table with unique references for the Referral number and was hoping to create the column in this table.

Is this logic only successful when used in a measure?

[fhill]

Both my examples were measures. Please post your exact table structures, and raw sample data from each table, so I can replicate your data source.

[JpTall · Accepted solution]

It is confidential data, so I can't share too much.

There are two tables:

1. Unique values for appointment reference (APPOINTMENTS)

2. Unique values for referral reference (REFERRALS)

Tables joined using the referral reference (*:1, single filter)

I think I have got a work around, using the thinking in your reply @fhill 

In the appointment table, I calculated a column to produce the date only when it's an install NOT cancelled:

Install date = IF(APPOINTMENT[App_Type_Code]<>"IN",BLANK(),IF(APPOINTMENT[Cancelled]="Y",BLANK(),APPOINTMENT[Appt_Date])).

Then in the Referrals table, I calculated a column to get the first install date:

First install date column = CALCULATE(FIRSTDATE(APPOINTMENT[Install date]))

This then creates a column that is accurate for each record in the table.

[fhill]

Glad you got it working.  If you are good, please mark resolved.

FOrrest