Simple (Hopefully) filter calculation

patri0t82 · ‎06-20-2023

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

SanderVeeken · ‎06-20-2023

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 · ‎06-20-2023

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 · ‎06-20-2023

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

CALCULATE(Expression, Filter1, Filter2, etc.)

patri0t82 · ‎06-20-2023

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 · ‎06-21-2023

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.