Helper 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 data for this measure which is from a different table.Native column measures gives correctly aggregated values in the chart.
Data Model as below:

Specific measure to Optimize:

Energy Values =

SUMX (

Prod_data_from_software,

VAR StartDateTime = Prod_data_from_software[Start Date Time]

VAR EndDateTime = Prod_data_from_software[End Date Time]

VAR InstantsTable =

CALCULATETABLE (

tm_periodflow_instants,

CROSSFILTER ( Prod_data_from_software[Meter_ID], 'Meter List'[Meter_ID], BOTH )

)

VAR FilteredInstantsTable =

FILTER (

InstantsTable,

tm_periodflow_instants[Date_Time] <= EndDateTime

&& tm_periodflow_instants[Date_Time] >= StartDateTime

)

RETURN

SUMX ( FilteredInstantsTable, tm_periodflow_instants[Value] )

)

Related query in DAX Studio for a sample value in slicer and the result :
EVALUATE

SUMMARIZECOLUMNS(
Prod_data_from_software[Machine],
TREATAS({"Thies-iMaster-1250Kg"},Prod_data_from_software[Machine]),
"Energy values",

SUMX (
    Prod_data_from_software,
    VAR StartDateTime = Prod_data_from_software[Start Date Time]
    VAR EndDateTime = Prod_data_from_software[End Date Time]

    VAR InstantsTable =
        CALCULATETABLE (
            tm_periodflow_instants,
            CROSSFILTER ( Prod_data_from_software[Meter_ID], 'Meter List'[Meter_ID], BOTH )
        )
    VAR FilteredInstantsTable =
        FILTER (
            InstantsTable,
            tm_periodflow_instants[Date_Time] <= EndDateTime
            && tm_periodflow_instants[Date_Time] >= StartDateTime
        )
    RETURN
        SUMX ( FilteredInstantsTable, tm_periodflow_instants[Value] )
)
)

VERTIPAQ Analyser metrics of the data:

The report takes huge amount of time to render .Deeply appreciate help on this.

Need Help

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
3 years ago
InsHunter

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

10 Replies

tamerj1
Community 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
  Helper 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
    Community Champion
    3 years ago
    InsHunter
    
    In this case I suggest to do it in SQL from the source
lbendlin
Super User
3 years ago
Enable query plan and server timing. Examine the query plan.

Please provide sanitized sample data that fully covers your issue.

InsHunter

Helper II

3 years ago

lbendlin
Hi Thanks for your response . Adding below the details as required .Apologies for the many screen shots and contents. Deeply appreciate your support.
Data model(small changes made 😞

Main Data set Screen shots:
First Fact Table :Prod_data_from_software

Second Fact Table : tm_periodflow_instants

Main Dim Table:

Created Basic Visual:(for only 3 months data)

Evaluated DAX query:
// DAX Query
DEFINE
  VAR __DS0FilterTable =
    TREATAS(
      {"Thies-iMaster-1000Kg",
        "Thies-iMaster-1250Kg",
        "Thies-iMaster-1500Kg",
        "Thies-iMaster-1750Kg",
        "Thies-iMaster-2000Kg"},
      'Meter List'[MeterName]
    )

  VAR __DS0FilterTable2 =
    FILTER(
      KEEPFILTERS(VALUES('Date Table'[Date])),
      'Date Table'[Date] < DATE(2021, 4, 1)
    )

  VAR __DS0Core =
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'Meter List'[MeterName],
          'Prod_data_from_software'[Batch],
          'Prod_data_from_software'[Shade],
          'Prod_data_from_software'[Start Date Time],
          'Prod_data_from_software'[End Date Time]
        ), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      "Prod_Hrs", 'Measures Table'[Prod Hrs],
      "Load_Kgs", 'Measures Table'[Load Kgs],
      "Energy_Values", 'Measures Table'[Energy Values],
      "Energy_per_Prod_Hr", 'Measures Table'[Energy per Prod Hr],
      "Energy_per_Kg", 'Measures Table'[Energy per Kg]
    )

  VAR __DS0PrimaryWindowed =
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'Prod_data_from_software'[Start Date Time],
      1,
      'Meter List'[MeterName],
      1,
      'Prod_data_from_software'[Batch],
      1,
      'Prod_data_from_software'[Shade],
      1,
      'Prod_data_from_software'[End Date Time],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'Prod_data_from_software'[Start Date Time],
  'Meter List'[MeterName],
  'Prod_data_from_software'[Batch],
  'Prod_data_from_software'[Shade],
  'Prod_data_from_software'[End Date Time]

Server Timings screen for the time taking query:

Query details:

SET DC_KIND="AUTO";
SELECT
'tm periodflow instants'[RowNumber], 'tm periodflow instants'[Date Time], 'tm periodflow instants'[Value], 'Meter List'[PortID], 'Meter List'[MeterTypeID], 'Meter List'[MeterID], 'Meter List'[MeterName], 'Meter List'[Group], 'Meter List'[LastReadTime], 'Meter List'[Formula], 'Meter List'[Column8], 'Meter List'[Meter ID]
FROM 'tm periodflow instants'
LEFT OUTER JOIN 'Meter List' ON 'tm periodflow instants'[Meter ID]='Meter List'[Meter ID]
WHERE
'Meter List'[Meter ID] IN ( 'TF_60', 'TF_61', 'TF_105', 'TF_47', 'TF_36' ) VAND
( 'Meter List'[PortID], 'Meter List'[MeterTypeID], 'Meter List'[MeterID], 'Meter List'[MeterName], 'Meter List'[Group], 'Meter List'[LastReadTime], 'Meter List'[Formula], 'Meter List'[Column8], 'Meter List'[Meter ID] ) IN { ( 107, 8, 105, 'Thies-iMaster-1750Kg', 'PROCESSING', '01-11-2022 12:07:00', null, null, 'TF_105' ) , ( 107, 8, 47, 'Thies-iMaster-1500Kg', 'PROCESSING', '01-11-2022 12:07:00', null, null, 'TF_47' ) , ( 107, 8, 60, 'Thies-iMaster-1000Kg', 'PROCESSING', '01-11-2022 12:07:00', null, null, 'TF_60' ) , ( 107, 81, 36, 'Thies-iMaster-2000Kg', 'PROCESSING', '01-11-2022 12:09:00', null, null, 'TF_36' ) , ( 107, 8, 61, 'Thies-iMaster-1250Kg', 'PROCESSING', '01-11-2022 12:07:00', null, null, 'TF_61' ) };

'Estimated size ( volume, marshalling bytes ) : 1828858, 87785184'

Logical query plan : (Attaching only a few lines due to words limitations)

Line	Logical Query Plan
1	__DS0FilterTable: TreatAs: RelLogOp VarName=__DS0FilterTable RefCount=2 DependOnCols()() 0-1 RequiredCols(0)('Meter List'[MeterName])
2	TableCtor: RelLogOp DependOnCols()() 0-1 RequiredCols(0, 1)(''[Value], ''[])
3	__DS0FilterTable2: Filter_Vertipaq: RelLogOp VarName=__DS0FilterTable2 DependOnCols()() 0-0 RequiredCols(0)('Date Table'[Date])
4	Scan_Vertipaq: RelLogOp DependOnCols()() 0-0 RequiredCols(0)('Date Table'[Date])
5	LessThan: ScaLogOp DependOnCols(0)('Date Table'[Date]) Boolean DominantValue=NONE
6	'Date Table'[Date]: ScaLogOp DependOnCols(0)('Date Table'[Date]) DateTime DominantValue=NONE
7	Constant: ScaLogOp DependOnCols()() DateTime DominantValue=01-04-2021
8	__DS0Core: Union: RelLogOp VarName=__DS0Core DependOnCols()() 0-11 RequiredCols(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)('Meter List'[MeterName], 'Prod_data_from_software'[Batch], 'Prod_data_from_software'[Shade], 'Prod_data_from_software'[Start Date Time], 'Prod_data_from_software'[End Date Time], ''[IsGrandTotalRowTotal], ''[Prod_Hrs], ''[Load_Kgs], ''[Energy_Values], ''[Energy_per_Prod_Hr], ''[Energy_per_Kg], ''[])
9	GroupSemiJoin: RelLogOp DependOnCols()() 0-10 RequiredCols(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)('Meter List'[MeterName], 'Prod_data_from_software'[Batch], 'Prod_data_from_software'[Shade], 'Prod_data_from_software'[Start Date Time], 'Prod_data_from_software'[End Date Time], ''[IsGrandTotalRowTotal], ''[Prod_Hrs], ''[Load_Kgs], ''[Energy_Values], ''[Energy_per_Prod_Hr], ''[Energy_per_Kg])
10	TableVarProxy: RelLogOp DependOnCols()() 0-1 RequiredCols(0)('Meter List'[MeterName]) RefVarName=__DS0FilterTable

lbendlin
Super User
3 years ago
Sorry, can't use any of this.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
- InsHunter
  Helper II
  3 years ago
  lbendlin
  Regret the incovenience. Sharing the google drive link for the PBIX file with one month data for understanding the issue better.
  https://drive.google.com/file/d/1z7xzSuv0v8-6h9FKJiQRmiIhQdyUGCB0/view?usp=share_link

Forum Discussion

Support to optimize the measure -DAX query

10 Replies

Related content

Optimize Measures

Optimize big measure

DAX Measure Optimization - Please help

DAX performance optimization

Fabric Cost optimization