Forum Discussion

HenryJS's avatar
HenryJS
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Add Column IF DATE AND TEXT

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 ...
data protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    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's avatar
    amitchandak
    6 years ago

    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])
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    HenryJS
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    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's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      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])