Solved: Add Filter

AllanBerces · ‎11-06-2025

Hi Good day can anyone help me amend my calculated column. base from the current calculated colum i want to filter one column on the same table (MAIN_DATA and column i want to filter is Category column and filter it not equal to MECH.

Under Category Column i have MECH, CIVIL, E&I and Plumber

Status =

var Derig = CALCULATE(MAX('MAIN_DATA'[Object Work %]),FILTER(ALLEXCEPT('MAIN_DATA','MAIN_DATA'[WON]),[BP Disp]="D. Scaff"))

var Paintng = CALCULATE(MAX('MAIN_DATA'[Object Work %]),FILTER(ALLEXCEPT('MAIN_DATA','MAIN_DATA'[WON]),[BP Disp]="Painting"))

var _Start = CALCULATE(AVERAGE('MAIN_DATA'[Object Work %]),ALLEXCEPT('MAIN_DATA','MAIN_DATA'[WON]))

var _Unknown = IF(NOT MAX(MAIN_DATA[BP Disp]) IN VALUES(MAIN_DATA[BP Disp]), 1, 0)

RETURN SWITCH(TRUE(),

Paintng>10&&Paintng<100&&_Start<>100,"Partial Completed",

Derig>10&&Derig<100&&_Start<>100,"Partial Completed",

_Start>0&&_Start<>100,"On-going",

_Start=0,"Not Started",

_Start=100,"Completed",

_Unknown = 1, "Unknown"

)

Thank you

Praful_Potphode · ‎11-06-2025

Hi @AllanBerces

with out seeing the data model and data, the solutions can be inaccurate sometimes.based on dax what i understood is that you want to exclude MECH from category.so it will look something like below:

Status =
-- Calculate max Object Work % for "D. Scaff" tasks excluding MECH category
VAR Derig =
    CALCULATE(
        MAX('MAIN_DATA'[Object Work %]),
        FILTER(
            ALLEXCEPT('MAIN_DATA', 'MAIN_DATA'[WON]),
            'MAIN_DATA'[BP Disp] = "D. Scaff"
                && 'MAIN_DATA'[Category] <> "MECH"
        )
    )

-- Calculate max Object Work % for "Painting" tasks excluding MECH category
VAR Paintng =
    CALCULATE(
        MAX('MAIN_DATA'[Object Work %]),
        FILTER(
            ALLEXCEPT('MAIN_DATA', 'MAIN_DATA'[WON]),
            'MAIN_DATA'[BP Disp] = "Painting"
                && 'MAIN_DATA'[Category] <> "MECH"
        )
    )

-- Calculate average Object Work % across all records for the same WON, excluding MECH
VAR _Start =
    CALCULATE(
        AVERAGE('MAIN_DATA'[Object Work %]),
        FILTER(
            ALLEXCEPT('MAIN_DATA', 'MAIN_DATA'[WON]),
            'MAIN_DATA'[Category] <> "MECH"
        )
    )

-- Flag unknown BP Disp values (not present in current context)
VAR _Unknown =
    IF(
        NOT MAX('MAIN_DATA'[BP Disp]) IN VALUES('MAIN_DATA'[BP Disp]),
        1,
        0
    )

-- Return status based on progress thresholds
RETURN
    SWITCH(
        TRUE(),
        -- If Painting or Derig is partially done and overall progress is not 100%
        Paintng > 10 && Paintng < 100 && _Start <> 100, "Partial Completed",
        Derig > 10 && Derig < 100 && _Start <> 100, "Partial Completed",

        -- If some progress is made but not fully completed
        _Start > 0 && _Start <> 100, "On-going",

        -- If no progress
        _Start = 0, "Not Started",

        -- If fully completed
        _Start = 100, "Completed",

        -- If BP Disp is unknown
        _Unknown = 1, "Unknown"
    )

If you check the code for variable derig, i have added the condition for MECH.the && operator is used to match multiple conditions.records matching all the conditions are returned.

If this doesn't help,please provide more information on data model,data,sample input snapshots,sample output snapshots.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

View solution in original post

DataNinja777 · ‎11-06-2025

Hi @AllanBerces ,

In order to exlude "MECH" from your filter, you can modify your calculated column as follows:

Status = 
VAR Derig = CALCULATE(
    MAX('MAIN_DATA'[Object Work %]),
    ALLEXCEPT('MAIN_DATA', 'MAIN_DATA'[WON]),
    'MAIN_DATA'[BP Disp] = "D. Scaff",
    'MAIN_DATA'[Category] <> "MECH" // <-- Filter added here
)
VAR Paintng = CALCULATE(
    MAX('MAIN_DATA'[Object Work %]),
    ALLEXCEPT('MAIN_DATA', 'MAIN_DATA'[WON]),
    'MAIN_DATA'[BP Disp] = "Painting",
    'MAIN_DATA'[Category] <> "MECH" // <-- Filter added here
)
VAR _Start = CALCULATE(
    AVERAGE('MAIN_DATA'[Object Work %]),
    ALLEXCEPT('MAIN_DATA', 'MAIN_DATA'[WON]),
    'MAIN_DATA'[Category] <> "MECH" // <-- Filter added here
)
VAR _Unknown = IF(NOT MAX(MAIN_DATA[BP Disp]) IN VALUES(MAIN_DATA[BP Disp]), 1, 0)

RETURN
    SWITCH(
        TRUE(),
        Paintng > 10 && Paintng < 100 && _Start <> 100, "Partial Completed",
        Derig > 10 && Derig < 100 && _Start <> 100, "Partial Completed",
        _Start > 0 && _Start <> 100, "On-going",
        _Start = 0, "Not Started",
        _Start = 100, "Completed",
        _Unknown = 1, "Unknown"
    )

With this change in calculated column, the result is as shown below:

The light-green highlighted status was assessed to be "Not started" because 106 only contained "MECH", and per your instruction "MECH" was to be excluded from the assessment.

I have attached a pbix file for your reference.