Exclude items from DAX query

[pnem]

EVALUATE
SELECTCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL ( 'DimItem'[Item Type], "h0" ),
            FILTER (
                VALUES ( DimItem[Season Code] ),
                OR (
                    ( @DimItemSeasonCode = "All" ),
                    PATHCONTAINS ( @DimItemSeasonCode, DimItem[Season Code] )
                )
            )
        ),
        "ParameterLevel", IF ( [h0], 0, 1 )
    ),
    "ParameterCaption", SWITCH ( [ParameterLevel], 1, "" & 'DimItem'[Item Type], "Blank()" ),
    "ParameterValue", "" & 'DimItem'[Item Type],
    "ParameterLevel", [ParameterLevel],
    "'DimItem'[Item Type]", 'DimItem'[Item Type]
)
ORDER BY
    'DimItem'[Item Type],
    [ParameterLevel]

I want to exclude item "Bag" from the list DimItemType

EVALUATE
SELECTCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL ( 
                FILTER('DimItem', 'DimItem'[Item Type] <> "BAG"), "h0" 
            ),
            FILTER (
                'DimItem',
                OR (
                    ( @DimItemSeasonCode = "All" ),
                    PATHCONTAINS ( @DimItemSeasonCode, 'DimItem'[Season Code] )
                )
            )
        ),
        "ParameterLevel", IF ( [h0], 0, 1 )
    ),
    "ParameterCaption", SWITCH ( [ParameterLevel], 1, "" & 'DimItem'[Item Type], "Blank()" ),
    "ParameterValue", "" & 'DimItem'[Item Type],
    "ParameterLevel", [ParameterLevel],
    "'DimItem'[Item Type]", 'DimItem'[Item Type]
)
ORDER BY
    'DimItem'[Item Type],
    [ParameterLevel]

Tried to add in filter to <> "Bag"  but I still get BAG inside the parameter list and in final result set.

Any advice?
Thanks!

[BeaBF]

@pnem Hi!
It looks like you added the filter on the 'DimItem' table after the ROLLUPADDISSUBTOTAL function, which is causing the "BAG" item to still appear in the final result set. To exclude "BAG" from the list of DimItemType, you need to apply the filter before the ROLLUPADDISSUBTOTAL function. Here's an updated version of the query that should work:

EVALUATE
SELECTCOLUMNS (
ADDCOLUMNS (
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
FILTER('DimItem', 'DimItem'[Item Type] <> "BAG"),
"h0"
),
FILTER (
FILTER('DimItem', 'DimItem'[Item Type] <> "BAG"),
OR (
( @DimItemSeasonCode = "All" ),
PATHCONTAINS ( @DimItemSeasonCode, 'DimItem'[Season Code] )
)
)
),
"ParameterLevel", IF ( [h0], 0, 1 )
),
"ParameterCaption", SWITCH ( [ParameterLevel], 1, "" & 'DimItem'[Item Type], "Blank()" ),
"ParameterValue", "" & 'DimItem'[Item Type],
"ParameterLevel", [ParameterLevel],
"'DimItem'[Item Type]", 'DimItem'[Item Type]
)
ORDER BY
'DimItem'[Item Type],
[ParameterLevel]

BBF

[pnem]

Thanks for the help, but I still get the error message - 

Advice? 🙂

[BeaBF]

@pnem 

ROLLUPGROUP (
FILTER (
FILTER('DimItem', 'DimItem'[Item Type] <> "BAG"),
OR (
( @DimItemSeasonCode = "All" ),
PATHCONTAINS ( @DimItemSeasonCode, 'DimItem'[Season Code] )
)
)
)


BBF

[pnem]

Still have the issue, any other solution? 😢

[BeaBF]

@pnem 

EVALUATE
SELECTCOLUMNS (
ADDCOLUMNS (
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( 'DimItem'[Item Type], "h0" ),
FILTER (
VALUES ( DimItem[Season Code] ),
OR (
( @DimItemSeasonCode = "All" ),
PATHCONTAINS ( @DimItemSeasonCode, DimItem[Season Code] )
)
)
),
"ParameterLevel", IF ( [h0], 0, 1 )
),
"ParameterCaption", SWITCH ( [ParameterLevel], 1, "" & 'DimItem'[Item Type], "Blank()" ),
"ParameterValue", "" & 'DimItem'[Item Type],
"ParameterLevel", [ParameterLevel],
"'DimItem'[Item Type]", 'DimItem'[Item Type]
),
-- add filter condition here to exclude "Bag"
DimItem[Item Type] <> "Bag"
)
ORDER BY
'DimItem'[Item Type],
[ParameterLevel]

BBF