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 | uniquekey2 | 0,67 | |
| 1 | uniquekey3 | 0,67 | 0,67 |
| 1 | uniquekey4 | 0,42 | 0,67 |
| 1 | uniquekey5 | 0,67 | |
| 1 | uniquekey6 | 0,67 | |
| 1 | uniquekey7 | 0,67 | |
| 1 | uniquekey8 | 0,67 | |
| 1 | uniquekey9 | 0,67 | |
| 1 | uniquekey10 | 0,34 | 0,67 |
| 1 | uniquekey11 | 0,67 | |
| 1 | uniquekey12 | 0,67 | |
| 1 | uniquekey13 | 0,67 | |
| 2 | uniquekey1 | 0,45 | |
| 2 | uniquekey2 | 0,45 | |
| 2 | uniquekey3 | 0,33 | 0,45 |
| 2 | uniquekey4 | 0,45 | 0,45 |
| 2 | uniquekey5 | 0,45 | |
| 2 | uniquekey6 | 0,45 | |
| 2 | uniquekey7 | 0,45 | |
| 2 | uniquekey8 | 0,45 | |
| 2 | uniquekey9 | 0,45 | |
| 2 | uniquekey10 | 0,34 | 0,45 |
| 2 | uniquekey11 | 0,45 | |
| 2 | uniquekey12 | 0,45 | |
| 2 | uniquekey13 | 0,45 |
"Selected" currently highlights a value of the Key's that are actively selected.
Instead "Max" finds the maximum "Selected" value, grouped by Record ID's. It is defined like this:
Max = calculate([Selected]; ALLEXCEPT(Table, Table[Record ID]))
This solution works - however it is a major problem, that this max function does not ignore the rows without any Selected value. This is because the data set is about one billion rows long. Is there any way to get this functionality without including the deselected Keys?
Thanks in advance!
11 Replies
- Perhaps:
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])))
Hi Darlove,
There are no calculated columns in my example, even though it is displayed like it.
My selected measure looks something like this:
Selected = calculate( max( Table[Value] ),
filter( Table[Key] in Union(values(Table1[Key]),
values(Table2[Key]),
values(Table3[Key])....
)))
So basically i have many slicers selecting which rows should be active. As it is a big dataset, one slicer cannot make all the selections, and still make sense for the use. So I cannot make an active relationship in the Data Model.
To elaborate, i have many tables in which the user can select slicers to define which row should be active. So each table returns one row or one key based on the slicer selection. These are then highlighted by Selected.
