optimization of measure codes

[Marek_Wojciech]

Hello,

I would like to ask about some tips on how I could optimize the code I wrote for the following issue to make it work faster (currently it takes far too much time):

I have a variable that is registered each day for each hour (columns 1 to 25 in the 'Generation' table). This variable can have value 0 (= means that system (column 'Generation'[Unit]) does not work at specific hour) or a value greater than 0 (= means that system works at this specific hour). I would like to count each time when the system switches off (Stop) and switches on (Start) during the day. In other words, I would like to count each change of the variable from zero value to a value grater than zero (= switch on) as well as count each change of this variable from a value greater than zero to zero value (= switch off). At the same time, I need to consider that each change from 0 value to value grater than zero at the turn of the day (midnight) has to be counted on the following day (= system start has been registered after midnight so on the new day) and the other way round - each change at midnight from value grater than 0 to 0 value has to be counted on the previous day (= system switched off before midnight so on the previous day).

As I mentioned before - I coded that already, but it works too slow. Please see my code below:

StartCount = 
VAR Daty = SUMMARIZE(Genertion,Genertion[Date],Genertion[Unit])
VAR col24p_1 =  
    CALCULATE (
        SUMX (
            Daty,
            IF (
                ISBLANK ( [24previewday] ),                                 //[24previewday] - measure returning the value from 24 hours of the previous day
                BLANK (),
                IF ( [Moc_1] > 0 && [24previewday] = 0, 1, BLANK () )
            )
        ),
        ALL ()
    )
VAR col25p_1 =                                                              
    // variable for autumn time change
    CALCULATE (
        SUMX (
            Daty,
            IF (
                ISBLANK ( [25previewday] ),                                 //[25previewday] - measure returning the value from 25 hours of the previous day
                BLANK (),
                IF ( [Moc_1] > 0 && [25previewday]= 0, 1, BLANK () )        //[Moc_n] - // measure returning the value from the nth hour of the current day etc, (n=1 to 25)
            )
        ),
        ALL ()
    )
VAR col1_2 =                                                                                // the variable takes into account the spring time change
    CALCULATE(SUMX(Daty,IF([Moc_1]=0&&IF([Moc_2]=Blank(),[Moc_3],[Moc_2])>0,1,Blank())))    
VAR col2_3 =                                                                                // the variable takes into account the spring time change
    CALCULATE(SUMX(Daty,IF(IF([Moc_2]=Blank(),[Moc_3],[Moc_2])=0&&[Moc_3]>0,1,Blank())))    
VAR col3_4 =
    CALCULATE(SUMX(Daty,IF([Moc_3]=0&&[Moc_4]>0,1,Blank())))
VAR col4_5 =
    CALCULATE(SUMX(Daty,IF([Moc_4]=0&&[Moc_5]>0,1,Blank())))
VAR col5_6 =
    CALCULATE(SUMX(Daty,IF([Moc_5]=0&&[Moc_6]>0,1,Blank())))
VAR col6_7 =
    CALCULATE(SUMX(Daty,IF([Moc_6]=0&&[Moc_7]>0,1,Blank())))
VAR col7_8 =
    CALCULATE(SUMX(Daty,IF([Moc_7]=0&&[Moc_8]>0,1,Blank())))
VAR col8_9 =
    CALCULATE(SUMX(Daty,IF([Moc_8]=0&&[Moc_9]>0,1,Blank())))
VAR col9_10 =
    CALCULATE(SUMX(Daty,IF([Moc_9]=0&&[Moc_10]>0,1,Blank())))
VAR col10_11 =
    CALCULATE(SUMX(Daty,IF([Moc_10]=0&&[Moc_11]>0,1,Blank())))
VAR col11_12 =
    CALCULATE(SUMX(Daty,IF([Moc_11]=0&&[Moc_12]>0,1,Blank())))
VAR col12_13 =
    CALCULATE(SUMX(Daty,IF([Moc_12]=0&&[Moc_13]>0,1,Blank())))
VAR col13_14 =
    CALCULATE(SUMX(Daty,IF([Moc_13]=0&&[Moc_14]>0,1,Blank())))
VAR col14_15 =
    CALCULATE(SUMX(Daty,IF([Moc_14]=0&&[Moc_15]>0,1,Blank())))
VAR col15_16 =
    CALCULATE(SUMX(Daty,IF([Moc_15]=0&&[Moc_16]>0,1,Blank())))
VAR col16_17 =
    CALCULATE(SUMX(Daty,IF([Moc_16]=0&&[Moc_17]>0,1,Blank())))
VAR col17_18 =
    CALCULATE(SUMX(Daty,IF([Moc_17]=0&&[Moc_18]>0,1,Blank())))
VAR col18_19 =
    CALCULATE(SUMX(Daty,IF([Moc_18]=0&&[Moc_19]>0,1,Blank())))
VAR col19_20 =
    CALCULATE(SUMX(Daty,IF([Moc_19]=0&&[Moc_20]>0,1,Blank())))
VAR col20_21 =
    CALCULATE(SUMX(Daty,IF([Moc_20]=0&&[Moc_21]>0,1,Blank())))
VAR col21_22 =
    CALCULATE(SUMX(Daty,IF([Moc_21]=0&&[Moc_22]>0,1,Blank())))
VAR col22_23 =
    CALCULATE(SUMX(Daty,IF([Moc_22]=0&&[Moc_23]>0,1,Blank())))
VAR col23_24 =
    CALCULATE(SUMX(Daty,IF([Moc_23]=0&&[Moc_24]>0,1,Blank())))
VAR col24_25 =                                                                          // variable for autumn time change
    CALCULATE(SUMX(Daty,IF([Moc_24]=0&&([Moc_25]>0&&[Moc_25]<>BLANK()),1,Blank())))

RETURN
col24p_1+col25p_1+col1_2+col2_3+col3_4+col4_5+col5_6+col6_7+col7_8+col8_9+col9_10+col10_11+col11_12+col12_13+col13_14+col14_15+
col15_16+col16_17+col17_18+col18_19+col19_20+col20_21+col21_22+col22_23+col23_24+col24_25

A similar measure applies to the number of stops.
Below is an example table:
Generation.xlsx

I would appreciate any hints on how to optimize that to make it work quicker, since my data base has  about 500,000 of records.

[amitchandak]

@Marek_Wojciech , Can you create another measure like,

CALCULATE(SUMX(Daty,
Switch( True(),
[Moc_2]=Blank(),[Moc_3],
[Moc_2])=0&&[Moc_3]>0,1,
[Moc_3]=0&&[Moc_4]>0,1,
//Addother
))

Another way to unpivot and have single condition

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

Also we can have table like

Sumx({[Col1], [Col2], [Col3]}, if( [Value] >1, 1,blank())

Add all column in {}

[Marek_Wojciech]

Thanks for all suggestions. I used the second one related to unpivoting facts’ table. I had to add a calculation column in the table. I thought of that before, but many power-users were not recommending such solution. As a result, query computing time decrased from 30 s to 0.5 s. Other suggested hints on modofying DAX code did not work out.