Optimizing measure

[nimp_kp]

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

[Sujit_Thakur]

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]

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]

Thanks @Anonymous  

But I was just trying to help 

I am also in learning phase .

Thanks for letting me know about it 😀

[mahoneypat]

Keep learning and proposing solutions @Sujit_Thakur .  We were all there at one point, and optimizing measures can be tough.

Regards,

Pat

[AntrikshSharma]

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

[mahoneypat]

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

[Anonymous]

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.

[lbendlin]

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.