Forum Discussion

HenryJS's avatar
HenryJS
Post 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 has a 'Export Actions'[ActionName] = "Call - Update"
  • The 'Export Actions'[ActionDate] is after 01/03/20

 

Thanks,

 

Henry

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Post 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
        Super 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])