Forum Discussion

nfuids's avatar
nfuids
New Member
3 years ago
Solved

Creating a Calculated Table using date from an existing Table

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's avatar
    v-jialluo-msft
    3 years ago

    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

4 Replies

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support
    3 years ago

    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.

  • nfuids's avatar
    nfuids
    New Member
    3 years ago

    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's avatar
    v-jialluo-msft
    Community Support
    3 years ago

    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's avatar
      nfuids
      New Member
      3 years ago

      Thank you, it worked perfectly for my needs!