Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Impactful Individual
1 year ago
Solved

Optimize AllSelected

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?
 
 
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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 ) )

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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 ) )