Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
InsHunter
Helper II
Helper II

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:

InsHunter_0-1668145221648.png

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] )
)
)
InsHunter_1-1668145407513.png

VERTIPAQ Analyser metrics of the data:

InsHunter_2-1668145481833.png

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










2 ACCEPTED SOLUTIONS

@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:

InsHunter_0-1668326334097.png

Performance now:

InsHunter_1-1668326410268.png

 

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.

 



 

View solution in original post

@InsHunter 

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

View solution in original post

10 REPLIES 10
tamerj1
Super User
Super User

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

@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:

InsHunter_0-1668326334097.png

Performance now:

InsHunter_1-1668326410268.png

 

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.

 



 

@InsHunter 

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

InsHunter
Helper II
Helper II

@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 😞

InsHunter_0-1668246229092.png

Main Data set Screen shots:
First Fact Table :Prod_data_from_software

InsHunter_1-1668246514977.pngInsHunter_2-1668246567250.png

Second Fact Table :  tm_periodflow_instants

InsHunter_3-1668246620682.png

Main Dim Table:

InsHunter_4-1668246821205.png

 

Created Basic Visual:(for only  3 months data)

InsHunter_5-1668247205991.png

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:

InsHunter_6-1668247310059.png

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)

LineLogical 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

 

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-...

@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 

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

lbendlin_0-1668274869602.png

 

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.

 

 

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

 

 

 

 

 

@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 .

InsHunter_0-1668328899706.png

 

Energy Values3.
This is getting very long time .

InsHunter_1-1668330264559.png

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





 

lbendlin
Super User
Super User

Enable query plan and server timing.  Examine the query plan.

 

Please provide sanitized sample data that fully covers your issue.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors