Forum Discussion

InsHunter's avatar
InsHunter
Icon for Helper II rankHelper II
3 years ago
Solved

Support to optimize the measure -DAX query

Hi, I got support from tamerj1  on constructing  below measure .Thanks to him.Now i need to optimize the measure and also there is difference in the aggregated value of 1.9 % against the original da...
Need Help
  • InsHunter's avatar
    InsHunter
    3 years ago

    tamerj1   Thanks for the suggestion.

    For simplicity used only 6 months data in the visual
    created calculated column as suggested with the following script:

    Batch =
    VAR batch_table_from_prod_meter = CALCULATETABLE (
            Prod_data_from_software,
            CROSSFILTER ( tm_periodflow_instants[Meter_ID], 'Meter List'[Meter_ID], BOTH ))

    VAR filtered_batch_table_from_prod_meter = FILTER (
                batch_table_from_prod_meter,
                tm_periodflow_instants[Date_Time] <= Prod_data_from_software[End Date Time]
                && tm_periodflow_instants[Date_Time] >= Prod_data_from_software[Start Date Time]
            )
    RETURN MAXX (filtered_batch_table_from_prod_meter, Prod_data_from_software[Batch]
    )
    Modified the earlier Measure :
    Energy Values 1 =
    SUMX (
        Prod_data_from_software,
        VAR FilteredInstantsTable =
            FILTER (
                tm_periodflow_instants,tm_periodflow_instants[Batch]=Prod_data_from_software[Batch]            
            )
        RETURN
            SUMX ( FilteredInstantsTable, tm_periodflow_instants[Value] )
    )

     

    Peformance earlier:

    Performance now:

     

    Thanks a lot for the suggestion and got tremendous improvement. Provided DAX to suggest for any redundancies.But calculated column took a lot of time as the instant data comes from SQL and currently has about 37 million records.

     



     

  • tamerj1's avatar
    tamerj1
    3 years ago

    InsHunter 

    In this case I suggest to do it in SQL from the source

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

    Here is a different view of your data model.  Can you confirm that tm_periodflow_instants is an independent fact table?

     

    Here is a simplistic version of the measure that doesn't work for the Total but gives you a basic idea of how to simplify your approach

     

    Energy Values2 = 
    VAR StartDateTime = min(Prod_data_from_software[Start Date Time])
    VAR EndDateTime = max(Prod_data_from_software[End Date Time])
    return CALCULATE(sum(tm_periodflow_instants[Value])
            ,tm_periodflow_instants[Date_Time] <= EndDateTime
            ,m_periodflow_instants[Date_Time] >= StartDateTime
    )

     

    I'll add a more precise version later.

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

       

       

      Energy Values2 =
VAR a =
    SUMMARIZE(
        Prod_data_from_software,
        [Meter_ID],
        [Start Date Time],
        [End Date Time]
    )
VAR b =
    ADDCOLUMNS(
        a,
        "sm",
            VAR st = [Start Date Time]
            VAR et = [End Date Time]
            VAR mid = [Meter_ID]
            RETURN
                CALCULATE(
                    SUM( tm_periodflow_instants[Value] ),
                    tm_periodflow_instants[Date_Time] <= et,
                    tm_periodflow_instants[Date_Time] >= st,
                    'Meter List'[Meter_ID] = mid
                )
    )
RETURN
    SUMX( b, [sm] )

       

       

      Looking at the query plan your query is much more efficient.  I'll have to go back to the drawing board. This variation of your query seems to have a really good query plan but poor performance. It does have a CallbackDataID though which is something you should avoid.  Let's see if  this can be improved upon.  Maybe give both versions a try and report back on performance.

       

       

       

       

      Energy Values3 = 
SUMX (
    Prod_data_from_software,
    VAR StartDateTime = Prod_data_from_software[Start Date Time]
    VAR EndDateTime = Prod_data_from_software[End Date Time]
    RETURN
        SUMX ( CALCULATETABLE (
            tm_periodflow_instants,
            CROSSFILTER ( Prod_data_from_software[Meter_ID], 'Meter List'[Meter_ID], BOTH ),
            tm_periodflow_instants[Date_Time] <= EndDateTime,
            tm_periodflow_instants[Date_Time] >= StartDateTime), 
        tm_periodflow_instants[Value] )
)

       

       

       

       

       

      • InsHunter's avatar
        InsHunter
        Icon for Helper II rankHelper II
        3 years ago

        @Ibendlin
        For simplicity used only 6 months data in the visual
        Thanks for your response and tried both the measures and outcome:
        Energy Values2.
        This is getting very long time .

         

        Energy Values3.
        This is getting very long time .

        Request to suggest alternates to the current measures  if possible.Thanks.