I have a very complex DAX calc I need help with

DAX DAX Calculations
1

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

2

@Nikki,

I’m wondering whether the relevant question is not how to optimize this DAX, but whether this should be done in DAX at all? Not knowing anything about your business process, I’m wondering how dynamic would these calculations be within the course of a single user session? (i.e., would you expect these values to remain constant between refresh cycles?). If the answer is yes, I would push as much of this “upstream” as possible, calculating it in Power Query rather than DAX, eliminating or dramatically reducing the performance issues.

– Brian

3

I actually have this is SSAS tabular

4

@Nikki,

Sorry – I have absolutely no experience with SSAS, but I’m looping in eDNA expert @Greg, whom I believe does.

  • Brian
5

If you have experience with PBI then you have experience with SSAS. Also she is just using SSAS for live connection and PBI for the measures.

6

@AntrikshSharma,

How does that affect the ability to manipulate the data in PQ, if at all?

  • Brian
7

@Nikki Since data isn’t available, this is best I can do: Not enough clarity/information to make changes to those smaller measures, and I don’t think much can be done for those IF/DIVIDE measures.

  Av St Len for CV Step1 :=
    CALCULATE (
        DIVIDE ( [Staple Length Coeffient of Variation] / 100, [Number of Lots] ),
        KEEPFILTERS (
            FILTER (
                ALL ( Fact_Fibre[WoolBase], Fact_Fibre[NetKg], Fact_Fibre[GreasyStapleLength] ),
                Fact_Fibre[WoolBase] > 0
                    && Fact_Fibre[NetKg] > 0
                    && Fact_Fibre[GreasyStapleLength] > 0
            )
        )
    )

.

Av St Len for CV Step2 :=
CALCULATE (
    SUMX (
        VALUES ( 'Lot Number'[LotNumber] ),
         ( [NetKG for CV Calc] * [Wool Base] )
    ),
    KEEPFILTERS (
        FILTER (
            ALL ( Fact_Fibre[WoolBase], Fact_Fibre[NetKg], Fact_Fibre[GreasyStapleLength] ),
            Fact_Fibre[WoolBase] > 0
                && Fact_Fibre[NetKg] > 0
                && Fact_Fibre[GreasyStapleLength] > 0
        )
    )
)

.

Av St Len for CV Step3 :=
CALCULATE (
    [Av St Len for CV Step1] * [Av St Len for CV Step1] + 1,
    KEEPFILTERS (
        FILTER (
            ALL ( Fact_Fibre[WoolBase], Fact_Fibre[NetKg], Fact_Fibre[GreasyStapleLength] ),
            Fact_Fibre[WoolBase] > 0
                && Fact_Fibre[NetKg] > 0
                && Fact_Fibre[GreasyStapleLength] > 0
        )
    )
)

.

Average Staple Length Coeffient of Variation :=
IF (
    [Greasy Staple Length] < 0,
    0,
    CALCULATE (
        SQRT ( [Av St Len for CV Step7] ) * 100,
        KEEPFILTERS (
            FILTER (
                ALL ( Fact_Fibre[WoolBase], Fact_Fibre[NetKg], Fact_Fibre[GreasyStapleLength] ),
                Fact_Fibre[WoolBase] > 0
                    && Fact_Fibre[NetKg] > 0
                    && Fact_Fibre[GreasyStapleLength] > 0
            )
        )
    )
)
8

Live connection doesn’t provide access to PQ in PBI, but SSAS has its own PQ as well I think it is version 1500 and above. For prior versions the interface is just like importing data directly from Power Pivot.

image
image1920×342 33.4 KB

So if you have data modelling tips for her then she can accommodate them in SSAS.

9

Nothing really to add @BrianJ … you and @AntrikshSharma have pretty-much said it all … do as much upstream as possible. One could also try, if possible, a similar query within SSAS to see if the holdup issue is actually SSAS itself (via the live connection) or PBI …
Greg

10

thanks for all the input guys. I’ll give feedback

11

Thanks It is an improvement. I will look into m-query. don’t have much experience with it

12

Hi @Nikki, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!