DAX to retrieve sum based on related table

[Anonymous]

Hi,

I am struggling to get my head around DAX.

I need to build a visual showing historical data.

So I have an "Item" table and an "Item History" table

The item table stores how many hours it takes to perform a job on the item and records its current status.

The history table records changes in status.

Edit : need to mention, the history record can have more than one record for the item_id,status,date (ie because it records other changes in history), so its not unique

I need to be able to retreive from the history table the date when the "Status" was "erected"  but get the prep hours from the Item table.

This then needs to show in a visual, summing hours by date.

Im assuming there should be a simple way to sum on this join, but I cant work it out.  any help?

dummy data below.  Hope that makes sense.

There is a one to many relationship from item to item_history

[tamerj1 · Accepted solution]

Hi @Anonymous 

Please refer to attached sample file with the solution

erection_date = 
CALCULATE ( 
    MAX ( item_history[date] ),
    FILTER ( 
        item_history,
        item_history[status] = "erected"
    )
)

prep_hours_measure = 
IF (
    NOT ISBLANK ( [erection_date] ),
    SUM ( item_id[prep_hours] )
)

[v-jialluo-msft · Accepted solution]

^{Hi @Anonymous ,}

^{This question can be solved using DAX functions, but it can also be solved directly using Filters, which is easier}

^{Here is the test data:}

^{Table relationships}

^{Use the Filters to filter the status}

^{If the “item_id” in the item table is unique, each “prep_hours” is also a unique value. The number of hours does not need to be summed. If not, please tell me how the hours are summed}

^{Based on the previous one, the total number of hours can be displayed by a measure}

SUM = 
SUMX (
    SUMMARIZE (
        'Item_History',
        [item_id],
        [status],
        [date],
        "1", SUM ( 'Item'[prep_hours] )
    ),
    [1]
)

^{Best Regards,}

^{Gallen Luo}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[tamerj1 · Accepted solution]

Hi @Anonymous 

Please refer to attached sample file with the solution

erection_date = 
CALCULATE ( 
    MAX ( item_history[date] ),
    FILTER ( 
        item_history,
        item_history[status] = "erected"
    )
)

prep_hours_measure = 
IF (
    NOT ISBLANK ( [erection_date] ),
    SUM ( item_id[prep_hours] )
)