topic DAX code to aggregate values of time stamps in one fact table based on time window in another table in DAX Commands and Tips

DAX code to aggregate values of time stamps in one fact table based on time window in another table

InsHunter — Tue, 08 Nov 2022 16:49:09 GMT

Hi,

Being a newbie struck in code for using two fact tables.

Have two fact tables and one dim table linked with common column Meter_ID.

Objective - Aggregate values against each time stamp of one fact table based on the date and time window(Between start time and end time) in another fact table for each Meter_ID.

"tm_periodflow_instants" table has Time stamp and Dates separately along with the values which need to be aggregated based on "Date" ,"Start Time" ,"End Time" of another fact table "Prod_data_from_software".
I could not complete the DAX formula for the measure which i started as below as related was not bringing the other fact table columns for filtering.

Energy Values =

CALCULATE(

SUMX(tm_periodflow_instants,tm_periodflow_instants[Value]),

FILTER(ALL(tm_periodflow_instants),tm_periodflow_instants[Time] < RELATED(P

)))

Deeply appreciate your support to complete the code
(For some reasons could not upload the data model images in the forum after repeated attempts)

Re: DAX code to aggregate values of time stamps in one fact table based on time window in another table

InsHunter — Tue, 08 Nov 2022 16:58:01 GMT

Each machine has the below data of times along with dates batch wise.

Value need to be aggregated based on the respective time stamps falling in the above window.

Re: DAX code to aggregate values of time stamps in one fact table based on time window in another table

tamerj1 — Tue, 08 Nov 2022 17:41:38 GMT

Hi InsHunter
Please try

Energy Values = SUMX ( Prod_data_from_software, VAR CurrentDate = Prod_data_from_software[Date] 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] = CurrentDate && tm_periodflow_instants[Time] <= Prod_data_from_software[End Time] && tm_periodflow_instants[Time] >= Prod_data_from_software[Start Time] ) RETURN SUMX ( FilteredInstantsTable, tm_periodflow_instants[Value] ) )

Re: DAX code to aggregate values of time stamps in one fact table based on time window in another table

InsHunter — Wed, 09 Nov 2022 02:17:25 GMT

tamerj1 Thanks . That was an immense help for me.God bless you. I think i need the date level granularity in both "Start Time" and "End Time" in my "Prod_data_from_software" table to get more accurate values when a batch on a machine is transcending the midnight. Hope in that case i need to remove the stand alone date in the filter of variable "FilterInstantsTable" and bring it to the time .right?

Re: DAX code to aggregate values of time stamps in one fact table based on time window in another table

tamerj1 — Wed, 09 Nov 2022 03:12:43 GMT

InsHunter

Yes. On this case DateTime shall be required in both tables (the three Time columns)

Re: DAX code to aggregate values of time stamps in one fact table based on time window in another table

InsHunter — Wed, 09 Nov 2022 03:41:03 GMT

tamerj1 Thank you very much.😀

Re: DAX code to aggregate values of time stamps in one fact table based on time window in another table

InsHunter — Wed, 09 Nov 2022 10:24:22 GMT

tamerj1 Hi.Sorry to bother u again. Since my "Prod_data_from_software" table had the time granularity in secs i rouned down to minutes to match with my "tm_periodflow_instants" table which is having the time stamp in minutes accepting some compromise. As date in "Prod_data_from_software" is end date ,start date was derived based on the time stamps and minutes field for dax purpose. I modified dax as below.

Energy Values =

SUMX (

Prod_data_from_software,

VAR StartDate = Prod_data_from_software[Start Date]

VAR EndDate = Prod_data_from_software[End Date]

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] <= EndDate

&& tm_periodflow_instants[Time] <= Prod_data_from_software[Rounded End Time]

&& tm_periodflow_instants[Time] >= Prod_data_from_software[Rounded Start Time]

&& tm_periodflow_instants[Date]>=StartDate

)

RETURN

SUMX ( FilteredInstantsTable, tm_periodflow_instants[Value] )

)

.But unfortunately not getting the values of batches crossing midnight.

Your help will be of great support for me. Thanks in Advance.

Re: DAX code to aggregate values of time stamps in one fact table based on time window in another table

tamerj1 — Wed, 09 Nov 2022 14:48:21 GMT

InsHunter

Please try

VAR FilteredInstantsTable = FILTER ( InstantsTable, tm_periodflow_instants[Time] <= Prod_data_from_software[Rounded End Time] && tm_periodflow_instants[Time] >= Prod_data_from_software[Rounded Start Time] )

Re: DAX code to aggregate values of time stamps in one fact table based on time window in another table

InsHunter — Thu, 10 Nov 2022 01:28:27 GMT

tamerj1 Thank you very much. I combined date and time in both data sets and used for the filter with start date as initial filter .Then it worked.It was a great help.