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

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi InsHunter 
You can try create a Batch column in the tm_periodflow_instants table. Then the aggregation would be pretty much simple and efficient.

Batch =
MAXX (
    CALCULATETABLE (
        Prod_data_from_software,
        CROSSFILTER ( tm_periodflow_instants[Meter_ID], 'Meter List'[Meter_ID], BOTH ),
        Prod_data_from_software[End Date_Time] >= tm_periodflow_instants[Date_Time],
        Prod_data_from_software[Start Date_Time] <= tm_periodflow_instants[Date_Time]
    ),
    Prod_data_from_software[Batch]
)
InsHunter's avatar
InsHunter
Icon for Helper II rankHelper II
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
    Icon for Community Champion rankCommunity Champion
    3 years ago

    InsHunter 

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