Forum Discussion
Measure not affected by slicer
Ah, that makes sense actually. Here's what's happening: ALLSELECTED('Table_name') clears filters off the whole invoice table, so then RANKX has to loop through every single invoice line and re-run a CALCULATE on each one just to figure out the ranking. If you've got tens of thousands of rows, that adds up fast and yeah, it'll choke on memory.
Try this instead - way lighter:
Invoice Rank by FY =
VAR CurrentFY = SELECTEDVALUE('Table_name'[Fiscal Year])
RETURN
RANKX(
ALLSELECTED('Table_name'[Customer]),
CALCULATE(
SUM('Table_name'[Amount Due]),
KEEPFILTERS('Table_name'[Fiscal Year] = CurrentFY)
),
,
DESC,
DENSE
)
The difference is I'm only stripping filters off the Customer column now instead of the entire table. So RANKX is only looping once per customer (probably a few dozen or a few hundred) instead of once per invoice row. Same ranking result, but it should run miles faster and stop chewing up memory. It also means your Invoice Type slicer stays untouched, so that should keep working too.
One more thing - if Customer lives in its own dimension table rather than being a column on the invoice table itself, let me know. You could point ALLSELECTED at that dimension table instead and it'd be even lighter, since Power BI wouldn't need to scan the fact table to find distinct customers.