Forum Discussion

patri0t82's avatar
patri0t82
Post Patron
3 years ago

Simple (Hopefully) filter calculation

Hello, Here's my formula. It's telling me the Expression refers to multiple columns.     Count-Jobs (NEW) = VAR SelectedYear = SELECTEDVALUE ( CalendarTable[Year] ) RETURN CALCULATE ( ...
Need Help
SanderVeeken's avatar
SanderVeeken
Helper III
3 years ago

Do you have some sample data to tinker with?
FILTER returns a table, not a single column. Also the arguments after the first of a CALCULATE statement are already filters, so I'm not sure you need all of the FILTER statements.

patri0t82's avatar
patri0t82
Post Patron
3 years ago

Thanks for responding. I'm not exactly sure I can get you sample data. I'd basically have to dummify my whole report with all its data. I'll try to come up with another solution before attempting that, if possible. I guess I'm just missing the point a little though, I mean, I'm trying to return a DISTINCTCOUNT based on some filter criteria - that seems like it would be a normal thing to do doesn't it?

  • SanderVeeken's avatar
    SanderVeeken
    Helper III
    3 years ago

    Have you tried it without all the explicit FILTER statements?
    Because basically CALCULATE already works that way;

    CALCULATE(Expression, Filter1, Filter2, etc.)

    • patri0t82's avatar
      patri0t82
      Post Patron
      3 years ago

      I believe this is a little bit cleaner, but I need to somehow incorporate a filter to ensure the ButaneSchedule table is filtered on the correct row, otherwise the code won't work.

                  FILTER ( ButaneSchedule, ButaneSchedule[Year] = SelectedYear ),



       

      Count-Jobs (NEW) = 
VAR SelectedYear =
    SELECTEDVALUE ( CalendarTable[Year] )
RETURN
    IF (
        SELECTEDVALUE ( CalendarTable[Date] ) >= ButaneSchedule[Begin]
            && SELECTEDVALUE ( CalendarTable[Date] ) <= ButaneSchedule[End],
        CALCULATE (
            DISTINCTCOUNT ( Jobs[JobUnique] ),
            FILTER (
                Jobs,
                Jobs[Area] = SELECTEDVALUE ( Areas[Area] )
                    && Jobs[JobAbbr] <> "V"
                    && Jobs[JobAbbr] <> "V1"
                    && Jobs[JobAbbr] <> "V2"
                    && Jobs[JobAbbr] <> "V3"
                    && Jobs[JobAbbr] <> "T"
                    && Jobs[JobAbbr] <> "S"
                    && Jobs[JobAbbr] <> "B"
                    && Jobs[JobAbbr] <> "O"
                    && Jobs[JobAbbr] <> "TA"
                    && Jobs[JobAbbr] <> "SE"
                    && Jobs[JobAbbr] <> "BT"
            )
        ),
        CALCULATE (
            DISTINCTCOUNT ( Jobs[JobUnique] ),
            FILTER (
                Jobs,
                Jobs[Area] = SELECTEDVALUE ( Areas[Area] )
                    && Jobs[JobAbbr] <> "V"
                    && Jobs[JobAbbr] <> "V1"
                    && Jobs[JobAbbr] <> "V2"
                    && Jobs[JobAbbr] <> "V3"
                    && Jobs[JobAbbr] <> "T"
                    && Jobs[JobAbbr] <> "S"
                    && Jobs[JobAbbr] <> "B"
                    && Jobs[JobAbbr] <> "O"
                    && Jobs[JobAbbr] <> "TA"
                    && Jobs[JobAbbr] <> "SE"
            )
        )
    )

       

       

      • SanderVeeken's avatar
        SanderVeeken
        Helper III
        3 years ago

        But I don't think you need the FILTER statements at all, since they are already part of the CALCULATE. So after your IF, it should be simply
        CALCULATE(COUNTROWS(Jobs[JobUnique]), Jobs[Area] = SELECTEDVALUE(Areas[Area]), Jobs[JobAbbr] <> "V", Jobs[JobAbbr] <> "V1", Jobs{JobAbbr) <> "V2" etc. etc.