bad performance of my dax equation

[Anonymous]

hello... I tried to calculate weighted income for quantiles.. it was very slow as the algorithim I took from a statistical expert.... the best I made was correct but very slow....

Q1 Income:=
CALCULATE (
sum(FACT_FAMILY[INCOME]),
FILTER (FACT_FAMILY,ROUNDUP(ABS(SUMX (
FILTER (FACT_FAMILY, [INCOME] <= EARLIER ([INCOME] ) ),
[FAMILY_WEIGHT]
)/(Sum(FACT_FAMILY[FAMILY_WEIGHT])/ 5)),0)=1))

-------------------------------------------------------------

Q2 Income:=
CALCULATE (
sum(FACT_FAMILY[INCOME]),
FILTER (FACT_FAMILY,ROUNDUP(ABS(SUMX (
FILTER (FACT_FAMILY, [INCOME] <= EARLIER ([INCOME] ) ),
[FAMILY_WEIGHT]
)/(Sum(FACT_FAMILY[FAMILY_WEIGHT])/ 5)),0)=2))

------------------------------------------------------------

Q3 Income:=
CALCULATE (
sum(FACT_FAMILY[INCOME]),
FILTER (FACT_FAMILY,ROUNDUP(ABS(SUMX (
FILTER (FACT_FAMILY, [INCOME] <= EARLIER ([INCOME] ) ),
[FAMILY_WEIGHT]
)/(Sum(FACT_FAMILY[FAMILY_WEIGHT])/ 5)),0)=3))

-----------------------------------------------------------

Q4 Income:=
CALCULATE (
sum(FACT_FAMILY[INCOME]),
FILTER (FACT_FAMILY,ROUNDUP(ABS(SUMX (
FILTER (FACT_FAMILY, [INCOME] <= EARLIER ([INCOME] ) ),
[FAMILY_WEIGHT]
)/(Sum(FACT_FAMILY[FAMILY_WEIGHT])/ 5)),0)=4))

----------------------------------------------------------

Q5 Income:=
CALCULATE (
sum(FACT_FAMILY[INCOME]),
FILTER (FACT_FAMILY,ROUNDUP(ABS(SUMX (
FILTER (FACT_FAMILY, [INCOME] <= EARLIER ([INCOME] ) ),
[FAMILY_WEIGHT]
)/(Sum(FACT_FAMILY[FAMILY_WEIGHT])/ 5)),0)=5))

-----------------------------------------------------------

Q1 to 5 income:=SWITCH(sum(DIM_QUANTILE[ID])
,1,CALCULATE([Q1 Income],all(DIM_QUANTILE))
,2,CALCULATE([Q2 Income],all(DIM_QUANTILE))
,3,CALCULATE([Q3 Income],all(DIM_QUANTILE))
,4,CALCULATE([Q4 Income],all(DIM_QUANTILE))
,5,CALCULATE([Q5 Income],all(DIM_QUANTILE)))

-------------------------------------------------------------

can anyone help me make it better and faster.. its very slow because of earlier function

[Anonymous]

Any solutions?

[v-yuta-msft]

@Anonymous ,

This measure has used a trick which can allow EARLIER be used in measure as well as calculate column, in the measure:

Q1 Income :=
CALCULATE (
    SUM ( FACT_FAMILY[INCOME] ),
    FILTER (
        FACT_FAMILY,
        ROUNDUP (
            ABS (
                SUMX (
                    FILTER ( FACT_FAMILY, [INCOME] <= EARLIER ( [INCOME] ) ),
                    [FAMILY_WEIGHT]
                )
                    / ( SUM ( FACT_FAMILY[FAMILY_WEIGHT] ) / 5 )
            ),
            0
        ) = 1
    )
)

There're two filters which means the table scanning times will be multiplied. The way to reduce the calculation time is to reduce table scanning in the DAX statement, for example, you may modify the measure into two calculate columns like pattern below:

Temp_Condition =
ROUNDUP (
    ABS (
        CALCULATE (
            SUM ( FACT_FAMILY[FAMILY_WEIGHT] ),
            FILTER ( FACT_FAMILY, [INCOME] <= EARLIER ( [INCOME] ) )
        )
            / ( SUM ( FACT_FAMILY[FAMILY_WEIGHT] ) / 5 )
    ),
    0
)

Q1 Income :=
CALCULATE (
    SUM ( FACT_FAMILY[INCOME] ),
    FILTER ( FACT_FAMILY, [Temp_Condition] = 1 )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello.,
Thank you for replay
Calculated columns will not serve me.. Since the part you take from the measure to the calculated column is the most important part to be dynamic not static... So if you put it in calculated column.. Quantile will not recalculated due to filter user choose... For example if you filter the report for gender to be male.. With calculated column will still has the commulative income for both male and female... And will give wrong results...
Thank you