topic Re: DATEDIFF When Usinf IF Argument in DAX Commands and Tips

DATEDIFF When Usinf IF Argument

RichBurdick — Wed, 26 Feb 2020 20:48:48 GMT

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

Re: DATEDIFF When Usinf IF Argument

Greg_Deckler — Wed, 26 Feb 2020 20:56:37 GMT

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 )

Re: DATEDIFF When Usinf IF Argument

RichBurdick — Wed, 26 Feb 2020 21:08:27 GMT

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.

Re: DATEDIFF When Usinf IF Argument

RichBurdick — Wed, 26 Feb 2020 22:01:06 GMT

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

Re: DATEDIFF When Usinf IF Argument

Greg_Deckler — Wed, 26 Feb 2020 22:14:45 GMT

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?

Re: DATEDIFF When Usinf IF Argument

RichBurdick — Wed, 26 Feb 2020 22:17:44 GMT

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.

Re: DATEDIFF When Usinf IF Argument

Greg_Deckler — Wed, 26 Feb 2020 22:20:41 GMT

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

Re: DATEDIFF When Usinf IF Argument

RichBurdick — Wed, 26 Feb 2020 22:24:25 GMT

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

Re: DATEDIFF When Usinf IF Argument

Greg_Deckler — Wed, 26 Feb 2020 23:46:35 GMT

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

Re: DATEDIFF When Usinf IF Argument

RichBurdick — Thu, 27 Feb 2020 00:07:12 GMT

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

Re: DATEDIFF When Usinf IF Argument

Greg_Deckler — Thu, 27 Feb 2020 02:03:17 GMT

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

Re: DATEDIFF When Usinf IF Argument

RichBurdick — Thu, 27 Feb 2020 04:59:04 GMT

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

Re: DATEDIFF When Usinf IF Argument

Greg_Deckler — Thu, 27 Feb 2020 05:21:31 GMT

Hope it worked for you!!

Re: DATEDIFF When Usinf IF Argument

RichBurdick — Thu, 27 Feb 2020 14:53:28 GMT

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 ?