Select the rows with the same ID's in the current week

[Sakis_Koukouvis]

Hi,
I have a table JOBS. It has kind of jobs.
Employees use this table to create rows in another table PROGRAM

The table JOB:
ID
POSITION OF EMPLOYEE (like gardener, officer etc.)
FREQUENCY (it has the enum list DAILY, WEEKLY, TWICE A WEEK, MONTHLY)

The table PROGRAM:
ID
DATE
JOB (connecting with the ID's of table JOB)
POSITION OF EMPLOYEE (copy from the table JOB)
FREQUENCY (copy from the table JOB)
TIME
PLACE

I want to select only the rows from the table JOB that have two rows (at least) with the frequency twice a week, in the current week in the table PROGRAM
I have a formula:
SELECT(PROGRAM[JOB], AND([JOB]=[JOB].[ID], [FREQUENCY]="TWICE A WEEK", WEEKNUM(TODAY())=WEEKNUM([DATE])))
But with this formula I have all the rows with frequency "twice a week" in current week but I can't see the rows that are double in the same week
How I could filter the rows having the jobs that thew have done twice a week in the current week?
Thank you
Sakis

[Suvrutt_Gurjar]

If understanding of your requirement is correct, your rows filter expression could be 

ISNOTBLANK(
FILTER(
"PROGRAM",
AND([_THISROW].[JOB] = [JOB], [FREQUENCY]="TWICE A WEEK", WEEKNUM(TODAY())=WEEKNUM([DATE] ))
)
- LIST([_THISROW])
)

[Sakis_Koukouvis]

@Suvrutt_Gurjar 

Thanks for the answer but I don't understand the [_THIS] = [JOB],. What is [_THIS}.

I have the error message "Unable to find column '_THIS', did you mean 'ID'?"

[Suvrutt_Gurjar]

There was a typo. Please check, I have updated the expression.

[Sakis_Koukouvis]

Sorry but I have error 

"Arithmetic expression '(AND(([_THISROW]........has inputs of an invalid type 'Unknown'"

[Suvrutt_Gurjar]

Could you share screenshot of the expression with the error in expression assistant?

Edit: I think there was a parenthesis missing in the expression. Could you try the revised expression?

ISNOTBLANK(
FILTER(
"PROGRAM",
AND([_THISROW].[JOB] = [JOB], [FREQUENCY]="TWICE A WEEK", WEEKNUM(TODAY())=WEEKNUM([DATE] ))
)
- LIST([_THISROW])
)

[Sakis_Koukouvis]

You couldn't understand because the name of the fields and the tables are in Greek. I have translated them in English to understand the problem.

But all the others are the same. 

Your expression in my case is:

ISNOTBLANK(
FILTER(
"ΠΡΟΓΡΑΜΜΑ",
AND([_THISROW].[ΕΡΓΑΣΙΑ]=[ΕΡΓΑΣΙΑ], [ΣΥΧΝΟΤΗΤΑ]="5.ΔΙΕΒΔΟΜΑΔΙΑΙΑ", WEEKNUM(TODAY())=WEEKNUM([ΗΜΕΡΟΜΗΝΙΑ])
)
- LIST([_THISROW])
))

and the message error is

Arithmetic expression '(AND(([_THISROW].[ΕΡΓΑΣΙΑ] = [ΕΡΓΑΣΙΑ]), ([ΣΥΧΝΟΤΗΤΑ] = "5.ΔΙΕΒΔΟΜΑΔΙΑΙΑ"), (WEEKNUM(TODAY()) = WEEKNUM([ΗΜΕΡΟΜΗΝΙΑ])))-LIST([_THISROW]))' has inputs of an invalid type 'Unknown'

[Suvrutt_Gurjar · Accepted solution]

Thank you , please try  the below expression. I think a few parentheses were incorrect in your expression.

Hope you re trying it as a row filter for example in a slice filter

ISNOTBLANK(
FILTER(
"ΠΡΟΓΡΑΜΜΑ",
AND([_THISROW].[ΕΡΓΑΣΙΑ]=[ΕΡΓΑΣΙΑ], [ΣΥΧΝΟΤΗΤΑ]="5.ΔΙΕΒΔΟΜΑΔΙΑΙΑ", WEEKNUM(TODAY())=WEEKNUM([ΗΜΕΡΟΜΗΝΙΑ]) )
)
- LIST([_THISROW])
)

[Sakis_Koukouvis]

Thank you @Suvrutt_Gurjar 👌

I have used this to a slice and I have the solution to filter the data.