cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Calculate second filtered by text column

Hi,

In this table I have 2 users that are in place a period of time. The "PremierUserDel" is in place the number of seconds in the column "WorkingTimePremierUser".
The "DeuxiemeUserDel"  is in place the number of seconds in the column "WorkingTimeDeuxiemeUser".

Capture.JPG

 

The expecting result for the time in place for each user for this period of time should be:

Marc                      3600

Micky                     8100

Pier-Luc                 3600

Danny                    3300

Autre equipe jour  7800

Xavier                     3600

 

How to calculate that?
Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

Hi @FRG ,

We could achieve your desired output in two ways: Dax and Power query.

First way: you could create the calculated table to achieve it.

Table =
UNION (
    SELECTCOLUMNS (
        'Table1',
        "user", 'Table1'[DeuxiemeUserDel],
        "workingtime", 'Table1'[WorkingTimeDeuxiemeUser]
    ),
    SELECTCOLUMNS (
        'Table1',
        "user", 'Table1'[PremierUserDel],
        "workingtime", 'Table1'[WorkingTimePremierUser]
    )
)

The second way, you could use append query under Query Editor.

1. Duplicate the table and then remove the columns don't need.

2. Rename the name to be the same for the two table and then click Append Queries.

More details, please refer to my attachment.

Here is the output.

Capture.PNG

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
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
Highlighted
Resident Rockstar
Resident Rockstar

Hi @FRG ,

We could achieve your desired output in two ways: Dax and Power query.

First way: you could create the calculated table to achieve it.

Table =
UNION (
    SELECTCOLUMNS (
        'Table1',
        "user", 'Table1'[DeuxiemeUserDel],
        "workingtime", 'Table1'[WorkingTimeDeuxiemeUser]
    ),
    SELECTCOLUMNS (
        'Table1',
        "user", 'Table1'[PremierUserDel],
        "workingtime", 'Table1'[WorkingTimePremierUser]
    )
)

The second way, you could use append query under Query Editor.

1. Duplicate the table and then remove the columns don't need.

2. Rename the name to be the same for the two table and then click Append Queries.

More details, please refer to my attachment.

Here is the output.

Capture.PNG

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
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

Highlighted

Thanks! That works!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors