Create column based on data from other columns

[amartinezdsq]

Hi. In my report, I am working with two tables. The first table is called "modlog"

The modlog table contains three columns.

mod_id = the modification id of the order

em_id = employee id of the person who modified the order

mod_datetime = timestamps of when the modification was done.

the second table is named "modlog_ship"

The "modlog_ship" table has five columns

ship_mod_id = the modification id for shipment events

mod_id = the modification id of the order

ds_id = the order number

mod_type = the type of modification made 

mod_value = the value of the modification made

Every order goes through several "mod_values"

I want to create two columns.

The first column would give me the "mod_datetime" when a "mod_value" of "AUDITED" was done. 

The second column would give me the "mod_datetime" when a "mod_value" of "BILLED" was done. 

The goal of my report is to show the following columns and count the number of days it took for the order to go from "AUDITED" to "BILLED"

ds_id = order number

mod_datetime when order had the mod_value of "AUDITED

mod_datetime when ordere had the mod_value of "BILLED"

Something like this

[v-luwang-msft · Accepted solution]

HI @amartinezdsq ，

Pls test the below dax to create a new table:

Table = 
DISTINCT (
    SELECTCOLUMNS (
        modlog_ship,
        "ds_id", modlog_ship[ds_id] ,
        "audited_date",
            LOOKUPVALUE (
                modlog[mod_datetime],
                modlog[mod_id],
                    CALCULATE (
                        MAX ( modlog_ship[mod_id] ),
                        FILTER (
                            modlog_ship,
                            modlog_ship[mod_value] = "AUDITED"
                                && modlog_ship[ds_id] = EARLIER ( modlog_ship[ds_id] )
                        )
                    )
            ),
        "BILLED",
            LOOKUPVALUE (
                modlog[mod_datetime],
                modlog[mod_id],
                    CALCULATE (
                        MAX ( modlog_ship[mod_id] ),
                        FILTER (
                            modlog_ship,
                            modlog_ship[mod_value] = "BILLED"
                                && modlog_ship[ds_id] = EARLIER ( modlog_ship[ds_id] )
                        )
                    )
            ),
        "#of date",
            DATEDIFF (
                LOOKUPVALUE (
                    modlog[mod_datetime],
                    modlog[mod_id],
                        CALCULATE (
                            MAX ( modlog_ship[mod_id] ),
                            FILTER (
                                modlog_ship,
                                modlog_ship[mod_value] = "AUDITED"
                                    && modlog_ship[ds_id] = EARLIER ( modlog_ship[ds_id] )
                            )
                        )
                ),
                LOOKUPVALUE (
                    modlog[mod_datetime],
                    modlog[mod_id],
                        CALCULATE (
                            MAX ( modlog_ship[mod_id] ),
                            FILTER (
                                modlog_ship,
                                modlog_ship[mod_value] = "BILLED"
                                    && modlog_ship[ds_id] = EARLIER ( modlog_ship[ds_id] )
                            )
                        )
                ),
                DAY
            )
    )
)

 Refer :

Output result:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[TomMartens]

Hey @amartinezdsq ,

please take the time and create a pbix file that contains sample data, but still reflects your data model, (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Do not forget to explain the expected results based on the sample data you provided.

The result you provided, contains two columns that i can not identify based on your sour data. 

Regards,

Tom