How to count a column only another column of the same records does not have value in the same table?

[mandynguyen]

I have a matrix as follows with help to modify the CONFIRM column:

PBIX FILE: https://drive.google.com/file/d/1ych6b8vYFm5XpL9cKVqT1KlPSCyN5WOW/view?usp=sharing

Background:

In my data set, every row is a student from different campus type. Each student may have all dates of Submit, Admit, Confirm, Enroll, and Withdraw or a some within the 5 options. All these date columns are connected many to one to Date column in Calendar Table.

Now in this matrix, I want to have counts of the 4 columns AS OF a specific date I choose (in the filter I set above).

For example, if I want all records since beginning to today-a-year-ago (6/23/2021), the matrix will be filtered ONLY records that have Submit, Admit, Confirm, Enroll dates before 6/23/2021, which I made it by creating new calculated measure for each date and "userelationship" setting.

But for "CONFIRM", I don't want purely count the number of date. I want to count the CONFIRM of records whose DROP column (in dataset not in matrix) is on or before the AS OF date.

For example:

- A student with confirm 06/01/2021, drop 06/30/2021, AS OF 06/23/2021, then this student should be counted in CONFIRM column.

- While a student with confirm 06/01/2021, drop 06/20/2021, AS OF 06/23/2021, then this student should NOT be counted in CONFIRM column.

So what should I do to achieve it? Please refer to my file for more details.

[v-yangliu-msft · Accepted solution]

Hi  @mandynguyen ,

You can modify measure[CONFIRM2] to the following form:

CONFIRM2 =
var _maxdate=MAXX(ALLSELECTED('Calendar'),[Date])
return
COUNTX(FILTER(ALL(Recruit),'Recruit'[CONFIRM]<=_maxdate&&'Recruit'[DROP]>=_maxdate  &&'Recruit'[CAMPUS]=MAX('Recruit'[CAMPUS])),[CAMPUS])

Because there is no data in your sample that meets this range, I will modify a date

Result：

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.