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