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