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
amartinezdsq
Regular Visitor

Create column based on data from other columns

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.

amartinezdsq_0-1654991048420.png

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

amartinezdsq_1-1654991163015.png

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

amartinezdsq_3-1654991631043.png

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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 :

vluwangmsft_0-1656384835353.png

vluwangmsft_1-1656384841342.png

 

Output result:

vluwangmsft_2-1656384859446.png

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


Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

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 :

vluwangmsft_0-1656384835353.png

vluwangmsft_1-1656384841342.png

 

Output result:

vluwangmsft_2-1656384859446.png

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


Best Regards

Lucien

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.