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

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

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)
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

 

View solution in original post

8 REPLIES 8
tamerj1
Super User
Super User

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

 

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

 

 

@InsHunter 

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

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

InsHunter_0-1667989410489.png

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

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

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

@tamerj1  Thank you very much.😀

InsHunter
Helper II
Helper II

InsHunter_0-1667926300877.png

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

InsHunter_1-1667926580184.png

 

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

InsHunter_2-1667926671604.png

 

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