performance issue with measure

[praveenpasila]

Hi All,

There is a huge performance issue and struggling for a while. Below is the scenario.

We have a Star schema data model with two facts table and two dimension table Data model as below

I have

C_Qty_1,  C_Qty_2, N_Qty, U_Qty in Fact Table 1

Amount in Fact Table 1

Flag column in Fact Table 1

Value_1, Value_2 in Fact Table 2

I have product (Dim Table 1) and Customer (Dim Table 2) in two different tables which are connected with Fact table 1

I create a matrix view with product Name and Customer Name and added Amount to the view and its working fine when I add below measure AGG_Measure with some filters added but when I unselect all filers and try to see the value it keeps on processing and says 

Resources Exceeded

This visual has exceeded the available resources.

Note: This works fine when I remove either Product or Customer from the visual and definitely we have huge number of rows returning as well in the visual when we remove filter like around a million rows.

I created a measure as below

AGG_Measure = 

var FH_Qty = Sum(C_Qty_1) + Sum(C_Qty_2)

var CST = Calculate(Divide(FH_Qty,Sum('Fact Table2'[Value_2 ],Filter('Fact Table1','Fact Table1'[Flag] =1))
var TSL =   Calculate (
                Divide ( Calculate ( [N_Qty], ALL(Dimesion Table1)),[Value_1]),

               'Fact Table2'

)

return IF ( CST <> 0 , CST, TSL )

Tried creating var FH_Qty and var TSL as seperate measure and referencing them in the above DAX but nothing seems to work

Do you know any way I can improve the performance of this measure?

Please suggest.

Thank you

[v-chenwuz-msft]

Hi @praveenpasila ,

Maybe you can try this:

AGG_Measure =
VAR FH_Qty =
    SUM ( 'Fact Table1'C_Qty_1 ) + SUM ( 'Fact Table1'C_Qty_2 )
VAR CST =
    CALCULATE (
        DIVIDE ( FH_Qty, SUM ( 'Fact Table2'[Value_2 ] ) ),
        KEEPFILTERS ( 'Fact Table1'[Flag] = 1 )
    )
VAR _a =
    CALCULATE ( [N_Qty], ALL ( DimesionTable1 ) )
VAR TSL =
    DIVIDE ( _a, [Value_1] )
RETURN
    IF ( CST <> 0, CST, TSL )

And please refer some articles about DAX Best Practice

Appropriate use of error functions in DAX - DAX | Microsoft Docs

DAX Best Practices | MAQ Software Insights

Best Regards

Community Support Team _ chenwu zhu

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

[praveenpasila]

Hi Chenwu zhu,

Thank you for your responce.

I changed the measure as mentoined but still no improvement in the performace getting the same out of memory error.

Can you please suggest if we can do anything else to increase the performance.

Also can you please help me understand as we have no direct join to Fact Table2 and Dimension Table1 might create issue.

Am I doing wrong in calling the var TSL rather than keeping it in a separate measure does this help in improving performance if we keep it seperately actually tried it but no help.