Forum Discussion
Solved
Counting Rows in one table that match multiple criteria in another table
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...
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:
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"
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]))