Forum Discussion
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 ...
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 ) )
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 ) )
johnt75 This helped a ton and solved my problems.
Thanks a lot.