cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ehsanbh
Helper III
Helper III

Combination of dates

Hello

 

I have three date columns in a dataset, which belong to different parameters (date 1 -> P1, P2 - date 2 -> P3, P4 - date 3 -> P5, P6). How can I combine them in a way that I only ended up in a date column (date -> P1, P2, P3, P4, P5, P6)? I also want to not have any dublication date in the final result. I tried to explain in the screenshots below.

Thank you for your tips on advanced!

 

 

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @ehsanbh ,

Based on your description, you can create a calculated table like this:

New Table =
VAR tab =
    ADDCOLUMNS (
        'Table',
        "Date",
            CALCULATE (
                MAX ( 'Table'[Date 1] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Date 1] = EARLIER ( 'Table'[Date 1] ) )
            )
    )
VAR tb =
    SUMMARIZE (
        tab,
        [Date],
        [Parameter 1],
        [Paremeter 2],
        [Paremeter 3],
        [Parameter 4],
        [Paremeter 5],
        [Parameter 6]
    )
RETURN
    tb

re.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yingjl
Community Support
Community Support

Hi @ehsanbh ,

Based on your description, you can create a calculated table like this:

New Table =
VAR tab =
    ADDCOLUMNS (
        'Table',
        "Date",
            CALCULATE (
                MAX ( 'Table'[Date 1] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Date 1] = EARLIER ( 'Table'[Date 1] ) )
            )
    )
VAR tb =
    SUMMARIZE (
        tab,
        [Date],
        [Parameter 1],
        [Paremeter 2],
        [Paremeter 3],
        [Parameter 4],
        [Paremeter 5],
        [Parameter 6]
    )
RETURN
    tb

re.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

ehsanbh
Helper III
Helper III

What I have tried myself is I duplicated the dataset two times, then keep date and related parameters in each dataset (and delete the rest): 

data set 1 => date 1, P1, P2

data set 2 => date 2, P3, P4

data set 3 => date 1, P5, P6

 

Then I changed name of date 1, date 2, and date 3 all to date and appened all three data sets. But I ended up in many dublication dates: 

 

5.JPG

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors