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 (
        DISTINCTCOUNT ( Jobs[JobUnique] ),
        FILTER (
            FILTER ( ButaneSchedule, ButaneSchedule[Year] = SelectedYear ),
            IF (
                SELECTEDVALUE ( CalendarTable[Date] ) >= ButaneSchedule[Begin]
                    && SELECTEDVALUE ( CalendarTable[Date] ) <= ButaneSchedule[End],
                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"
                ),
                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"
                )
            )
        )
    )

 

 

 

 

Basically all I'm looking to do is "if the selected calendar date falls within the date range then distinct count based on *these* filters, otherwise, disctinctcount based on *those* filters

Need Help

5 Replies

  • 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.)