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"
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: