Filter value in a new column

[Anonymous]

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 pic you can see there is a list of activity to select from but I only need a few in my new column and their duration as a result.   

  

I also want this to sum the same activity for the day from the same source (name) and date. 

Please can you advise, I am really unable to move forward from this and your help is much appreciated. 

Thank you 

[v-kelly-msft]

Hi  @Anonymous ,

For example ,if you just wanna get the duration for the activity of "Login",you could create a column as below:

Column = 
 SWITCH('Table'[Activity],
 "Login",CALCULATE(DATEDIFF(MAX('Table'[Start ]),MAX('Table'[End]),DAY)),
 BLANK())

And you will see:

You can add other criteria after "switch" function if needed, and you can also use "If "function instead.

For the sample .pbix file,pls see attached.

Best Regards,
Kelly

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

[Anonymous]

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 · Accepted solution]

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]

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 

 

[VijayP]

@Anonymous 

If I understood your problem Statement Correctly, you require a new column with fewer items. 

You can use a custom column with Switch Function to have the Grouped items , Liked "Abondoned","Break" as one item and so on. Then your slicer will be based on new column with fewer items and accordingly the values will be
Let me know if this solves!

[amitchandak]

@Anonymous , Create a new column like

sumx(filter(table, [activity] = earlier([activity]) && [Source] = earlier([Source]) && [Day] = earlier([Day])), hour([Duration])*3600+minute([Duration])*60 + second([duration]))

Also check for Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[Anonymous]

Thank you, I am not getting it right. Please can I confirm what would be the [source] and [Day]. Also, where do I get to filter the selected options from 'activity' column?

Sorry, I am very new to PowerBI and pretty basic. Thanks