Forum Discussion

nimp_kp's avatar
nimp_kp
Frequent Visitor
6 years ago

Optimizing measure

Hi

 

I have made the following measure, used in a matrix, that gives me the correct data.

But because of the massive amount of data in the dataset, it takes several minutes to display the data and often runs out of memory.

 

Any tips on how to optimize it for better performance? Would VAR/RETURN help?

 

Thanks.

Measure = 
IF(
    HASONEVALUE(Table_1[Column_1]),
    IF(
        [Measure_1] = 0.5
         && ([Measure_2]-[Measure_3] >= 0)
         && [Measure_4] = 1
       ,IF(
            CALCULATE(DISTINCTCOUNT(Table_2[Column_1]), Table_2[Column_2] = "Y") >= 1
           ,[Measure_5]
           )
       )
  )

 

DAX
if statement
Measures

8 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    Use DAX Studio to examine the query plan. That will help you decide what to change and how. You have lots of sub-measure black boxes.

     

    You want to minimize the number of records looped, and maximize the SE/FE ratio.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    It's not possible to optimize this code since this measure depends on many other measures the structures of which have not been revealed. On top of that, to optimize a measure one has to have a good understanding of the underlying model since many a time in order to optimize a measure structural changes to the model itself must be made.
  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    I agree it is hard to suggest changes without seeing the expressions for the referenced measures.  One thing you can do to narrow it down, is to put hard-code values in place for the measures and see which one(s) are causing the slowness.  You can then maybe post that expression for advice on optimization (also share some details about your model (# rows and pic of relationships on tables used in the measure(s)).

    You can also watch one of the SQLBI.com videos on optimizing measures.

    https://www.sqlbi.com/tv/

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    6 years ago

    nimp_kp One thing you can try is to replace DISTINCTCOUNT with SUMX.

     

    Measure =
IF (
    HASONEVALUE ( Table_1[Column_1] ),
    IF (
        [Measure_1] = 0.5
            && ( [Measure_2] - [Measure_3] >= 0 )
            && [Measure_4] = 1,
        IF (
            CALCULATE (
                SUMX (
                    VALUES ( Table_2[Column_1] ),
                    1
                ),
                Table_2[Column_2] = "Y"
            ) >= 1,
            [Measure_5]
        )
    )
)

     

  • Sujit_Thakur's avatar
    Sujit_Thakur
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Dear nimp_kp 

     

    Use this and try maybe it could help 


    Measure =
    Var A = Measure 1
    Var B = Measure 2
    Var C = Measure 3
    Var D = Measure 4
    var E = Measure 5
    Var test = IF(
    HASONEVALUE(Table_1[Column_1]) && A= 0.5 && B-C >=0 && D=1 && CALCULATE(DISTINCTCOUNT(Table_2[Column_1]), Table_2[Column_2] = "Y") >= 1
    ,
    E
    )
    Return test

     

     

     

    Let me know if this helped .

     

    Give kudos to motivate ! 

    And if it helped please accept as solution .

     

    Regards 

    Thakur Sujit 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Hi Sujit_Thakur

      Your version is even worse than the original. You should *not* calculate measures before IF unless you know ALL OF THEM will certainly be used later. This is not the case here. In other words, you're suggesting the opposite of optimization.
      • Sujit_Thakur's avatar
        Sujit_Thakur
        Icon for Solution Sage rankSolution Sage
        6 years ago

        Thanks Anonymous  

        But I was just trying to help 

        I am also in learning phase .

        Thanks for letting me know about it 😀