I’ve written a really awful DAX query that grinds the PowerBI to a halt. IT takes up to half an hour to load the page. I know there’s a better way to do it.

Please can someone help me optimise it.

It consists of 7 steps

Av St Len for CV Step1:=

CALCULATE (

DIVIDE ( [Staple Length Coeffient of Variation] / 100, [Number of Lots] ),

FILTER ( Fact_Fibre, Fact_Fibre[WoolBase] > 0 ),

FILTER ( Fact_Fibre, Fact_Fibre[NetKg] > 0 ),

FILTER(Fact_Fibre,Fact_Fibre[GreasyStapleLength] >0)

)

Av St Len for CV Step2:=

CALCULATE (

SUMX (

VALUES ( ‘Lot Number’[LotNumber] ),

( [NetKG for CV Calc] * [Wool Base] )

),

FILTER ( Fact_Fibre, Fact_Fibre[WoolBase] > 0 ),

FILTER ( Fact_Fibre, Fact_Fibre[NetKg] > 0 ),

FILTER ( Fact_Fibre, Fact_Fibre[GreasyStapleLength] > 0)

)

Av St Len for CV Step3:=

CALCULATE (

[Av St Len for CV Step1] * [Av St Len for CV Step1] + 1,

FILTER ( Fact_Fibre, Fact_Fibre[WoolBase] > 0 ),

FILTER ( Fact_Fibre, Fact_Fibre[NetKg] > 0 ),

FILTER ( Fact_Fibre, Fact_Fibre[GreasyStapleLength] > 0 )

)

Av St Len for CV Step4:= IF([Av St Len for CV Step3] <= 1, BLANK(), [Av St Len for CV Step3])

Av St Len for CV Step5:=

SUMX(VALUES(‘Lot Number’[LotNumber]),( [Av St Len for CV Step2] * [Greasy Staple Length] * [Av St Len for CV Step4]))

Av St Len for CV Step6:= DIVIDE([Av St Len for CV Step5],([Average Staple LengthFor CV Calc] * [Av St Len for CV Step2]))

Av St Len for CV Step7:= IF( [Av St Len for CV Step6] - 1 < 0 , 0, [Av St Len for CV Step6] - 1 )

Average Staple Length Coeffient of Variation:= IF([Greasy Staple Length] <0,0,

CALCULATE (

SQRT ( [Av St Len for CV Step7] ) * 100,

FILTER ( Fact_Fibre, Fact_Fibre[WoolBase] > 0 ),

FILTER ( Fact_Fibre, Fact_Fibre[NetKg] > 0),

FILTER ( Fact_Fibre, Fact_Fibre[GreasyStapleLength] > 0)

))