Forum Discussion

NeuroFtr's avatar
NeuroFtr
New Member
3 years ago
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...
  • NeuroFtr's avatar
    NeuroFtr
    3 years ago

    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's avatar
NeuroFtr
New Member
3 years ago

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's avatar
    NeuroFtr
    New Member
    3 years ago

    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:
  • NeuroFtr's avatar
    NeuroFtr
    New Member
    3 years ago

    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]))