Optimize AllSelected

[ThomasWeppler]

Hi Power BI community

I have writen some DAX that uses the allselected option, It works but it is way to slow.

I categorize customers in 9 categories and then look at how they have preformed the last X months.

I have a table called Parameter[Parameter] with the numbners from 0 to 36 where the client can choose how far the want to look back.

All customers have a valule ABC calculated like this.

ABC =

var _scope = IF(ISINSCOPE('4327 Main customer'[Name]), 1,0)

var _Invoice = IF([Sumx Invoice] > DIVIDE(600000, SELECTEDVALUE(Parameter[Parameter],1)), "A",

IF([Sumx Invoice] > DIVIDE(200000, SELECTEDVALUE(Parameter[Parameter],1)), "B", "C"))

var _coverage rate = IF([Main customer coverage rate] > 0.5, "A", IF([main customer coverage rate] > 0.1, "B", "C"))

 Return

IF( _scope = 1 && [Sumx Invoice] > 0, _Invoice & _coverage rate,BLANK())

Then I have this filter on my table that allow me to only shows the customers in the categorize choosen by the end user. It gives me the value 1 or 0 depending on what categorize are choosen and I make sure to only show the customers with value 0

ABC Correct =

var _selected = ALLSELECTED(ABC[ABC])

RETURN

IF(ISBLANK([ABC]), BLANK(),

if([ABC] in _selected,1,0))

Any ideas how I can optimize my DAX so it runs faster?

[johnt75 · Accepted solution]

You can speed it up by storing values that you use multiple times in variables

ABC =
VAR ChosenParameter =
    SELECTEDVALUE ( Parameter[Parameter], 1 )
VAR InvoiceSum = [Sumx Invoice]
VAR MainCoverageRate = [Main customer coverage rate]
VAR _scope =
    IF ( ISINSCOPE ( '4327 Main customer'[Name] ), 1, 0 )
VAR _Invoice =
    IF (
        InvoiceSum > DIVIDE ( 600000, ChosenParameter ),
        "A",
        IF ( InvoiceSum > DIVIDE ( 200000, ChosenParameter ), "B", "C" )
    )
VAR _coverage_rate =
    IF ( MainCoverageRate > 0.5, "A", IF ( MainCoverageRate > 0.1, "B", "C" ) )
RETURN
    IF ( _scope = 1 && InvoiceSum > 0, _Invoice & _coverage_rate, BLANK () )

ABC Correct =
VAR ABC = [ABC]
VAR _selected =
    ALLSELECTED ( ABC[ABC] )
RETURN
    IF ( ISBLANK ( ABC ), BLANK (), IF ( ABC IN _selected, 1, 0 ) )

[ThomasWeppler]

@johnt75 This helped a ton and solved my problems.

Thanks a lot.