Count different values in different columns, based on an equal date.

[TeisL]

Hi,

To give you some background information:
I'm trying to report the number of Job Requisitions (Job Req ID) who where published (job post name) internally (Job Posting: Internal posting) and externally (Job Posting: External Posting) on the same date (Posting Start Date).

Example:

In this example following job req id's should be counted:

• 104588 = 1 double posting
• 94584 = 1 double posting
• 119917 = 0 double posting
• 31630 = 0 double posting
• 64128 = 0 double posting

Looking forward to your help!

Teis

[wdx223_Daniel · Accepted solution]

In M code, you can try this

NewStep=Table.Group(PreivousStepName,"Job Req ID",{"Double Posting Count",each List.NonNullCount(Table.Group(_,"Posting Start Date",{"n",each if List.ContainsAll([Job Post Name],{"Internal",External"},(x,y)=>Text.Contains(x,y)) then 1 else null})[n])})