Add Column IF DATE AND TEXT

[HenryJS]

Hi all,

I have two tables:

• Export Candidates
• Export Actions

The two are joined by 'CandidateRef'

I want to create a column on Export Candidates that states YES IF:

• The CandidateRef has a 'Export Actions'[ActionName] = "Call - Update"
• The 'Export Actions'[ActionDate] is after 01/03/20

Thanks,

Henry

[amitchandak · Accepted solution]

@HenryJS 

new column in Export Candidates
new column = if(isblank(maxx(filter('Export Actions','Export Actions' [CandidateRef] ='Export Candidates'[CandidateRef]
&& 'Export Actions'[ActionName] = "Call - Update" && 'Export Actions'[ActionDate]>= date(2020,03,01)),
'Export Actions' [CandidateRef])),"No","Yes")

[amitchandak · Accepted solution]

@HenryJS 

new column in Export Candidates
new column = if(isblank(maxx(filter('Export Actions','Export Actions' [CandidateRef] ='Export Candidates'[CandidateRef]
&& 'Export Actions'[ActionName] = "Call - Update" && 'Export Actions'[ActionDate]>= date(2020,03,01)),
'Export Actions' [CandidateRef])),"No","Yes")

[HenryJS]

Thanks @amitchandak  that's perfect!

How can I now add an additional column which returns (or states) the latest 'Export Actions'[ActionDate] for the most recent "Call - Update for that candidate?

So it will be a column with a date.

Relating to the most recent date the "Call - Update" occured.

Cheers! Hope you have a good weekend

[amitchandak]

@HenryJS ,

Refer, if this can help

maxx(filter('Export Actions','Export Actions' [CandidateRef] ='Export Candidates'[CandidateRef]
							&& 'Export Actions'[ActionName] = "Call - Update" && 'Export Actions'[ActionDate]>= date(2020,03,01)),
							'Export Actions' [ActionDate])

[HenryJS]

@amitchandak got the below error

[amitchandak · Accepted solution]

I suggested a column. But in case you are looking as measure. Try one of the following

maxx(filter('Export Actions','Export Actions' [CandidateRef] =related('Export Candidates'[CandidateRef])
							&& 'Export Actions'[ActionName] = "Call - Update" && 'Export Actions'[ActionDate]>= date(2020,03,01)),
							'Export Actions' [ActionDate])

maxx(filter('Export Actions','Export Actions' [CandidateRef] =max('Export Candidates'[CandidateRef])
							&& 'Export Actions'[ActionName] = "Call - Update" && 'Export Actions'[ActionDate]>= date(2020,03,01)),
							'Export Actions' [ActionDate])		

maxx(filter('Export Actions','Export Actions'[ActionName] = "Call - Update" && 'Export Actions'[ActionDate]>= date(2020,03,01)),
							'Export Actions' [ActionDate])