Forum Discussion

trine_norris's avatar
trine_norris
Frequent Visitor
6 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago
Can you please show me your [Selected] measure?

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

Best
D
trine_norris's avatar
trine_norris
Frequent Visitor
6 years ago

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's avatar
    trine_norris
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        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