Optimizing the allexcept function

[trine_norris]

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!

[Anonymous]

Can you please show me your [Selected] measure?

I suspect you are talking about calculated columns here, not measures.

Best
D

[trine_norris]

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.

[trine_norris]

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.

[Anonymous]

First, try to change the measure to:

Selected =
var __allSelectedValues =
	Union(
	  values(Table1[Key]), 
	  values(Table2[Key]),
	  values(Table3[Key]),
	  ... // however many you need to put in here
	)
var __result =
	calculate(
		MAX( Table[Value] ),
		KEEPFILTERS(
	        TREATAS(
	        	__allSelectedValues,
	        	Table[Value]
	        )
	    )
)
return
	__result

Without relationships this is the fastest code you can get (TREATAS).

Best

D

[Anonymous]

If your table with billions of rows is the one above... then how are you presenting it to the end user? Are you slicing by Record Id as well to just show a couple of Record Id's?

This whole stuff seems to me to be a bit fishy... If there's a big table and you're creating a measure for it that needs to use CALCULATE, then it'll be slow as hell because of the number of context transitions that need to take place.

Best
D

[Greg_Deckler]

Perhaps:
Max =
VAR __Max = calculate([Selected]; ALLEXCEPT(Table, Table[Record ID]))
RETURN IF(ISBLANK([Selected]),BLANK(),__Max)

[trine_norris]

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.

[Greg_Deckler]

OK, try this, this should be faster:
Max =
IF(ISBLANK([Selected]);BLANK();
calculate([Selected]; ALLEXCEPT(Table, Table[Record ID])))

[Greg_Deckler]

This might be even better:

Max =
IF([Selected]=BLANK());BLANK();
calculate([Selected]; ALLEXCEPT(Table, Table[Record ID])))

[Greg_Deckler]

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

[trine_norris]

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? 🙂