Summarize with AND/OR Filter

[GSE]

Hi all,

I am trying to use the sumx and then the summarize function on a table. However, I want to filter the table first. The data that meets the following criteria should be excluded.

1. dim_house_id = 8

2. dim_house_id = 12 AND dim_class_id = 4

Both dim tables (house and class) are in a 1-to-many relationship with the fact table. I'm not sure how to filter here.

SUMX(SUMMARIZE(FILTER(fact table, RELATED(dim_house_id) <> 8 || RELATED(dim_house_id) <> 12 && RELATED(dim_class_id) <> 4)...

It's not so much about the sumx or summarize function but I don't know how to set up the filter criteria.

Any help would be appreciated.

Thanks

[GSE]

@AlexisOlson thank you so much. This is a very good and intuitive solution!

[Greg_Deckler]

@GSE Try:

FILTER(fact table, RELATED(dim_house_id) <> 8 || ( RELATED(dim_house_id) <> 12 && RELATED(dim_class_id) <> 4 ) )

[AlexisOlson · Accepted solution]

"FILTER(fact table, RELATED(dim_house_id) <> 8 || ( RELATED(dim_house_id) <> 12 && RELATED(dim_class_id) <> 4 ) )"

This doesn't filter anything out much like (x <> 1 || x<>2) is true for any value of x. (You need to swap your && and || to get the proper De Morgan's law negation.)

I'd propose this as a more intuitive option:

FILTER (
    'fact table',
    NOT (
          RELATED ( dim_house[house_id] ) = 8  ||
        ( RELATED ( dim_house[house_id] ) = 12 && RELATED ( dim_class[class_id] ) = 4 )
    )
)