Forum Discussion
Optimizing the allexcept function
Hi, I have some data (Record ID and Key) and two measures (Selected and Max), which currently looks something like this: Record ID Key Selected Max 1 uniquekey1 0,67 1 uniq...
It now returns the right thing, but is not really quicker, as it still calculates on all rows in the variabel.
I was thinking that something like:
Max = Calculate(Selected, distinct(Record ID))
should work, but of course that syntax is invalid.
OK, try this, this should be faster:
Max =
IF(ISBLANK([Selected]);BLANK();
calculate([Selected]; ALLEXCEPT(Table, Table[Record ID])))
Max =
IF(ISBLANK([Selected]);BLANK();
calculate([Selected]; ALLEXCEPT(Table, Table[Record ID])))
This might be even better:
Max =
IF([Selected]=BLANK());BLANK();
calculate([Selected]; ALLEXCEPT(Table, Table[Record ID])))I just posted an article on DAX performance tuning that might help:
https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
Hi Greg,
Thanks for your replies, though unfortunately they only slow the measure down.
I was hoping for way to use allexcept with a filter.
Anyone? 🙂