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...
Perhaps:
Max =
VAR __Max = calculate([Selected]; ALLEXCEPT(Table, Table[Record ID]))
RETURN IF(ISBLANK([Selected]),BLANK(),__Max)
Max =
VAR __Max = calculate([Selected]; ALLEXCEPT(Table, Table[Record ID]))
RETURN IF(ISBLANK([Selected]),BLANK(),__Max)
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])))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