Forum Discussion
Faster method for double boolean on filter?
Got this requirement that a source file contains per postal code a categorization. However, sometimes for a particular label, all postal codes are allowed. So I created something like this:
minx (
filter ( 'source file'
, 'source file'[country] = 'fact'[postal]
&& if ( 'source file'[postal code] = "all" , true() , 'source file'[postal] = 'fact'[postal] )
)
, 'source file'[value]
)
But this one seems to eat memory/ time like madness. Anyway to speed up this type of filter?
5 Replies
DouweMeer Try using
MINX(
FILTER(
'source file',
'source file'[country] = 'fact'[country] &&
('source file'[postal code] = "all" || 'source file'[postal code] = 'fact'[postal])
),
'source file'[value]
)This approach uses a logical OR (||) to check if the postal code is "all" or matches the postal code in the fact table, which should be more efficient than using an IF statement inside the filter.
My postal check isn't as easy as I made it seem. It is like:
left ( 'fact'[postal] , len ( 'source'[postal] ) ) = 'source'[postal]Was thinking of not having it verify the postal when the source file's info is all.
Whilst writing this, I came up with another question:
https://community.fabric.microsoft.com/t5/Desktop/Order-in-which-filter-works/m-p/4405425#M1371776
Would you know that question's answer?
Is it faster to check on both at the same time instead of my prior of if ( equal all , true , otherwise ) ?
M = VAR __FactCountry = 'fact'[country] VAR __FactPostal = 'fact'[postal] VAR _FilteredTable = FILTER ( SUMMARIZE ( 'source file', 'source file'[country], 'source file'[postal code], 'source file'[value] ), 'source file'[country] = __FactCountry && ( 'source file'[postal code] = "all" || 'source file'[postal] = __FactPostal ) ) RETURN MINX ( _FilteredTable, 'source file'[value] )Why do you work with VAR's in this scenario? Aren't VAR's stored in memory during operation? Wouldn't that suck in capacity?
