count records with same dates to give yes/no flag

[dpc1]

Hi

I'm pulling data into a table and trying to create a value to show if a maintenance task has been completed on a single date.

so in the example below i have the maintenance request code, engineer and dates they finished.  where the dates are all the same for each request i want so show that's its been fixed on the same day, where a request had different dates for the engineer visits it was not completed on the same day.

So i'm trying to create an expression to give a yes/no to show if the maintenance request was a first day fix, so it should look like the table below.   (the first 3 columns are fields/dims from the load script, the 4th column I'm trying to create in the table expressions)

can anyone help with an expression to write for this, thank you

Work Request Code	Engineer	Date Finished	First day Fix
2391162	JAMES VEVERS	05/07/2022	Yes
2391162	LUKE ELLIOT	05/07/2022	Yes
2391162	NICHOLAS CANAVAN	05/07/2022	Yes
2391171	BOB FAIRCLOUGH	01/07/2022	No
2391171	BOB FAIRCLOUGH	06/07/2022	No
2391171	KENAN ROBINSON	01/07/2022	No
2391171	KENAN ROBINSON	06/07/2022	No

[Bill_Britt]

Hi,

I would think you would need another column for the StartDate.  Then you can use an if statement. 

if([Date started]=[Date Finished] ,'yes','no') as[First day Fix]

See attached QVW.

[MarcoWedel · Accepted solution]

One solution using a straight table expression might be:

If(Count(DISTINCT TOTAL<[Work Request Code]> [Date Finished])=1,'Yes','No')

hope this helps

Marco

[sidhiq91]

@MarcoWedel  Could you please explain what this expression actually does?

Thanks in advance.

[MarcoWedel]

The expression assigns a value of 'Yes' to all Work Request Codes having only one single distinct Date Finished and a value of 'No' to all other Work Request Codes.

@dpc1 , is this your expected result?

[sidhiq91]

@MarcoWedel  Thanks. Appreciate your explanation as always.

[dpc1]

thank you just what we need 😊