Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
VERTIPAQ Analyser metrics of the data:
The report takes huge amount of time to render .Deeply appreciate help on this.
Solved! Go to Solution.
@tamerj1 Thanks for the suggestion.
For simplicity used only 6 months data in the visual
created calculated column as suggested with the following script:
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.
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:
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.
@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 |
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-...
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?
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 .
Energy Values3.
This is getting very long time .
Request to suggest alternates to the current measures if possible.Thanks.
Enable query plan and server timing. Examine the query plan.
Please provide sanitized sample data that fully covers your issue.
User | Count |
---|---|
64 | |
27 | |
25 | |
17 | |
11 |