DATEDIFF When Usinf IF Argument

[RichBurdick]

I am trying to wrote a DAX measurement where based on the text value of a column use these two date fields to calculate days.

I have been able to figure out the DATEDIFF part of the measurement but have been running into issues with the IF. 

Here is the measurement:

CAPA Planning Days = IF(CAPATable[Record_Source] = “Audit”), (DATEDIFF(MIN(CAPATable[DATE_OPENED]), MAX(CAPAPlanFirstApproval[FirstDate]), DAY))

The DateDiff works fine but could use some assistance on the IF part 

[Greg_Deckler]

Well, if this is a measure, you are going to need to have an aggregation like MIN or MAX around your column. I think you want something like:

CAPA Planning Days = 
  IF(
    MAX(CAPATable[Record_Source]) = “Audit”, // true/false statement
    DATEDIFF(MIN(CAPATable[DATE_OPENED]), MAX(CAPAPlanFirstApproval[FirstDate]), DAY), //true
    "This is the false part" //false
  )

[RichBurdick]

Thanks for the response but when I enter the new argument I get the following error message: Too few arguments were passed to the IF function. The minimum argument count for the function is 2.

[RichBurdick]

Maybe more information would help.  I am trying to create a measure with the following criteria

If Record Source = Audit
(DATEDIFF(MIN(CAPATable[DATE_OPENED]), MAX(CAPAPlanFirstApproval[FirstDate]), DAY)) and
(DATEDIFF(MIN(CAPAtable[DATE_OPENED]), MAX(CorrFirstApproval[FirstDate]), DAY))
OR
If Record Source = Quality Issue
(DATEDIFF(MIN(CAPARequiredTable[DATE_PERFORMED], MAX(CAPATable[CAPA_PLAN_APPROVAL_ON]), DAY))

[Greg_Deckler]

So that generally means there is a missing paren somewhere. I do not understand your last post at all. If the record source = audit then a datediff and another datediff? I am not understanding something. Are you adding those datediffs together?

[RichBurdick]

So basicaly I need to make one of three different calculations based on the record source.  If the record source is from an audit it can be either one of the two calculations but if the record source is a quality issue use that calculation.

[Greg_Deckler]

OK, so what is the criteria for which calculation to use for Audit?

[RichBurdick]

Hi Greg,

I need to use both.  Maybe there is a better function or order to use.

If Record Source = Audit
(DATEDIFF(MIN(CAPATable[DATE_OPENED]), MAX(CAPAPlanFirstApproval[FirstDate]), DAY)) 
OR
If Record Source = Audit
(DATEDIFF(MIN(CAPAtable[DATE_OPENED]), MAX(CorrFirstApproval[FirstDate]), DAY))
OR
If Record Source = Quality Issue
(DATEDIFF(MIN(CAPARequiredTable[DATE_PERFORMED], MAX(CAPATable[CAPA_PLAN_APPROVAL_ON]), DAY))

[Greg_Deckler]

Right but when do you use one versus the other? For Audit.

[RichBurdick]

Sorry, I guess that would help.

If Record Source = Audit and Status = Closed-Done
(DATEDIFF(MIN(CAPATable[DATE_OPENED]), MAX(CAPAPlanFirstApproval[FirstDate]), DAY)) 
OR
If Record Source = Audit and Status – Closed- Correction
(DATEDIFF(MIN(CAPAtable[DATE_OPENED]), MAX(CorrFirstApproval[FirstDate]), DAY))
OR
If Record Source = Quality Issue
(DATEDIFF(MIN(CAPARequiredTable[DATE_PERFORMED], MAX(CAPATable[CAPA_PLAN_APPROVAL_ON]), DAY))

[Greg_Deckler]

OK, I think that I would do something like this:

CAPA Planning Days = 
  VAR __RecordSource = MAX(CAPATable[Record_Source])
  VAR __Status = MAX(CAPATable[Status])
RETURN
  SWITCH(
    TRUE(),
    __RecordSource = "Audit" && __Status = "Closed-Door",DATEDIFF(MIN(CAPATable[DATE_OPENED]), MAX(CAPAPlanFirstApproval[FirstDate]), DAY),
    __RecordSource = "Audit" && __Status = "Closed-Correction",DATEDIFF(MIN(CAPATable[DATE_OPENED]), MAX(CorrFirstApproval[FirstDate]), DAY),
    __RecordSource = "Quaity Issue",DATEDIFF(MIN(CAPARequiredTable[DATE_PERFORMED]), MAX(CAPAPTable[CAPA_PLAN_APPROVAL_ON]), DAY),
    BLANK()
  )

[RichBurdick]

Thank you so much for the help.  I have been racking my brain for hours on this.  

[Greg_Deckler]

Hope it worked for you!!

[RichBurdick]

Quick follow up queston. I am seeing a problem with the calculation, is there anything special I need to do if using a Summary Table in my calculation ?