Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter value in a new column

Hi,  I am trying to create a new column where I can filter and select multiple options from the 'activity' column which would give me the result of 'duration' value in my new column.    From my p...
Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Anonymous,

     

    Sorry for a late reply!

    You wanna sum up all the activities which contain "break",right?If so,you could use below dax expression:

    Create 2 calculated columns as below:

    flag = SEARCH("Break",[Activity],1,0)

    Column = SUMX(FILTER('Table','Table'[flag]>0),[Duration])

     And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi, thank you for getting back to me. 

I have managed create a new column by using switch where it is looking at each each "xxxx" and bringing their duration value. But I need my new column to sum up all the e.g. "break" taken by each member on the same date. Please can you advise? 

 

Time in AUX =
SWITCH('Enghouse'[Activity],
"Break", Enghouse[Duration],
"Requested Worktime (Administration)", Enghouse[Duration],
"Break (Lunch)", Enghouse[Duration],
"Requested Worktime (Meeting)", Enghouse[Duration],
"Break (Scheduled Break)", Enghouse[Duration])

 

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous,

 

Sorry for a late reply!

You wanna sum up all the activities which contain "break",right?If so,you could use below dax expression:

Create 2 calculated columns as below:

flag = SEARCH("Break",[Activity],1,0)

Column = SUMX(FILTER('Table','Table'[flag]>0),[Duration])

 And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi, thank you for your reply. Unfortunately, I am getting 00:00:00 as a value. 

     

    This is an example of what I need in the new the column, it is very easy to do this in excel by creating a pivot anfd filtering out which I have been doing previously but finding it quite difficult in PowerBI. 

     

    As you can see it needs to sum the duration of each activity performed on the same date. 

     

    Appreciate your help, thank you