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