Counting Rows in one table that match multiple criteria in another table

[NeuroFtr]

I have a table named Fly_Plan with date value columns named START and END, a column named Prgm, and a column named CAT.

I want a DAX formula that creates NewTable from Fly_Plan that returns START, END, and Prgm when CAT = "Plan". I want it to calculate a third column named AvailCount that counts every record where Fly_Plan[CAT] = "Avail" and Fly[Plan]END <= NewTable[start] and Fly_Plan[end] >= NewTable[start].  After much research the best I can come up with is the below formula which returns no values for AvailCount.  

Table = SUMMARIZE(

  FILTER(Fly_Plan, Fly_Plan[CAT] = "Plan"),

  Fly_Plan[START],

  Fly_Plan[END],

  Fly_Plan[Prgm],

  "AvailCount",

  CALCULATE(

    DISTINCTCOUNT(Fly_Plan[CAT]),

        FILTER(

            Fly_Plan,

            Fly_Plan[CAT] = "Avail"

            && Fly_Plan[Start] <= EARLIER(Fly_Plan[END])

            && Fly_Plan[End] >= EARLIER(Fly_Plan[Start])

        && Fly_Plan[Prgm] = EARLIER(Fly_Plan[Prgm])

    )

  )

)

This is what the output looks like:

As you can see I would expect that the 2 red values circled in the first example would meet the criteria and produce an AvailCount of 2 on the blue circled date in the new table of 12/8/2022.  Any help would be greatly appreciated.

[scee07]

Hi, it is all a matter of taste and there are always several ways to reach the goal. Here a few remarks what concerns my taste:

a) if you are not really summarizing (aggregating) keep the table or a copy of it for the extensions you do. Above it looks like that you are happy to keep your rows. So,  a calculated column seems reasonable.

b) at least I use only the various commands like EARLIER, .... if I really must. At the end of the day they are all syntactic sugar for some logic you want to quickly use.

c) in the calculated columns I prefer a programmatic approach (again all matter of taste). I put the row context in variables and can then program whatever I like:

AvailCount = 
var thisStart = Fly_Plan[START]
var thisEnd = Fly_Plan[END]
var thisPrgrm = Fly_Plan[Prgm]
var thisCat = Fly_Plan[CAT]

return 
COUNTROWS(filter(Fly_Plan, Fly_Plan[START] >= thisStart && .....)) // whatever your conditions are

In case I only need the row context, I like this approach better. You can calculate what you want and you see immediately what you use as row input or other input in the variables.

You can  then filter the result afterwards in a visual or to a new table to see the desired result.

Best regards 

Christian

[NeuroFtr · Accepted solution]

Ok I figured it out. I needed first create a new table called Capacity with this formula: 

Capacity = SUMMARIZE(

    FILTER(Fly_Plan,Fly_Plan[CAT]="Plan"),

    Fly_Plan[START],

    Fly_Plan[END],

    Fly_Plan[Prgm]

)

Then I created a new column called AvailCount inside Capacity with this formula:

CALCULATE (COUNTROWS(Fly_Plan), FILTER( all(Fly_Plan[End]),Fly_Plan[End]>=Capacity[Start]), FILTER( all(Fly_Plan[Start]),Fly_Plan[Start]<=Capacity[End]),FILTER( all(Fly_Plan[Prgm]),Fly_Plan[Prgm]=Capacity[Prgm]),FILTER( all(Fly_Plan[CAT]),Fly_Plan[CAT]="Avail"))

Which gave me the correct output even after adding some new test entries:

[NeuroFtr]

Ok I see part of my problem.  It won't count "Avail" because it has already filtered out "Plan" in the beginning of the formula, but I still don't know how to only show the dates for "Plan" while counting "Avail"

[NeuroFtr]

I think I am making it too complicated.  Maybe I don't even need to make a new table when I can possibly get it work with just a new column?  Using the below formula I still only get a count of 1 next to each individual record and not an aggregate count across the entire table for all records that match the criteria.  I guess that is why I thought I needed a new table to compare new records to the old (EARLIER).

CALCULATE (COUNTROWS(Fly_Plan), FILTER( all(Fly_Plan[End]),Fly_Plan[Start]<=Fly_Plan[End]), FILTER( all(Fly_Plan[Start]),Fly_Plan[End]>=Fly_Plan[Start]))

[NeuroFtr]

So if I try CountRows and don't filter at the beginning the formula is still not aggregating the count for all records in the old table that match the criteria listed for the new table.

SUMMARIZE(

  Fly_Plan,

  Fly_Plan[START],

  Fly_Plan[END],

  Fly_Plan[Prgm],

  Fly_Plan[CAT],

  "AvailCount",

  CALCULATE(

    COUNTROWS(Fly_Plan),

        FILTER(

            Fly_Plan,

            Fly_Plan[Start] <= EARLIER(Fly_Plan[END])

            && Fly_Plan[End] >= EARLIER(Fly_Plan[Start])

        && Fly_Plan[Prgm] = EARLIER(Fly_Plan[Prgm])

    )

  )

)

With the output looking like: