Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 )
)
)
@GSE Try:
FILTER(fact table, RELATED(dim_house_id) <> 8 || ( RELATED(dim_house_id) <> 12 && RELATED(dim_class_id) <> 4 ) )
"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 )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |