Creating a Calculated Table using date from an existing Table

[nfuids]

Hi,

I'm trying to figure out how to create a calculated table using data from an existing table.

Existing table has the following column

Title, StartDateDEV, EndDateDev, StartDateINTG, EndDateINTG, StartDateUAT, EndDateUAT

For each row of the existing table, I would like to create a row for each pair of date. So if the DEV dates exists, I would like to create a row that has

Title, "DEV", StartDate, EndDate (using DEV Dates)

If StartDateINTG/EndDateINTG exists, I would also add

Title, "INTG", StartDate, EndDate (using INTG Dates)

If StartDateUAT/EndDateUAT exists, I would also add

Title, "UAT", StartDate, EndDate (using UAT Dates)

I'm not sure where to start.. any pointer would be appreciated

Here is a schema

[v-jialluo-msft · Accepted solution]

^{Hi @nfuids ,}

^{You can also use the following writing.}

Calculate table =
FILTER (
    UNION (
        SELECTCOLUMNS (
            'Table',
            "Title", [Title],
            "StartDate", [StartDateDev],
            "EndDate", [EndDateDev],
            "Category", "Dev"
        ),
        SELECTCOLUMNS (
            'Table',
            "Title", [Title],
            "StartDate", [StartDateUAT],
            "EndDate", [EndDateUAT],
            "Category", "UAT"
        ),
        SELECTCOLUMNS (
            'Table',
            "Title", [Title],
            "StartDate", [StartDateINTG],
            "EndDate", [EndDateINGT],
            "Category", "INGT"
        )
    ),
    [StartDate] <> BLANK ()
        && [EndDate] <> BLANK ()
)

^{The only difference between the two writing is that SUMMARIZE will deduplicate the data and SELECTCOLUMNS will not, and if this has no effect on your data, then there is no difference.}

SELECTCOLUMNS - DAX Guide

^{Best Regards,}

^{Gallen Luo}

[nfuids]

Thank you, it worked perfectly for my needs!

[nfuids]

Super, I'll play with this and let you know!

why are you using selectcolums for Dev dates and then use SUMMARIZE for the other 2?

thanks

[v-jialluo-msft]

^{Hi @nfuids ,}

^{Please follow these steps:}

^{(1) Create a new table}

Calculate table = 
FILTER (
    UNION (
        SELECTCOLUMNS (
            'Table',
            "Title", [Title],
            "StartDate", [StartDateDev],
            "EndDate", [EndDateDev],
            "Category", "Dev"
        ),
        SUMMARIZE ( 'Table', [Title], [StartDateUAT], [EndDateUAT], "Category", "UAT" ),
        SUMMARIZE (
            'Table',
            [Title],
            [StartDateINTG],
            [EndDateINGT],
            "Category", "INGT"
        )
    ),
    [StartDate] <> BLANK ()
        && [EndDate] <> BLANK ()
)

^{(2)Final output}

 

^{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.}