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, EndDateI...
  • 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

v-jialluo-msft's avatar
v-jialluo-msft
Icon for Community Support rankCommunity 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.