Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am using a time Dax measure taken from here: https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486#comments
It works wonderfully, but there is an issue I have encountered when using within a Pivot Table and then filtering. As I filter and the data set shrinks I find that users who should be filtered out as they are no longer in the selected date range still reflect as ::
This must be because the concatenate part of the expression therefore still shows as a value "::"
I tried creating a calculate formula to filter out the :: but it did not do anything, I'm new to Dax and I'm assuming my attempt was hamfisted.
Dur_No_Dot = CALCULATE(Chat[Duration],FILTER(chat,[Duration] <> "::"))
So yes, pasted below is the code creating the formula, if anyone can tell me how to surpress the :: on a pivot table I'd much appreciate the help!
Duration = // Duration formatting
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = Chat[chat_in_seconds]
// There are 3,600 seconds in an hour
VAR Hours =
INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes =
INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours
VAR Seconds =
ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
CONCATENATE (
H,
CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
)
Solved! Go to Solution.
@amitchandakThe solutions you have linked are excellent, in something like a KPI they work flawlessly. The base issue upon filtering within a pivot table remains.
In effect these measure create string values and when filtering down if there is no information for a specific user they reflect the string without the time data. So, in my case it was ::, in the first link you provided it shows as " Hours & Minutes & Seconds".
What I need is a way to filter my pivot table to exclude the specific string value of "Hours & Minutes & Seconds" or "::".
But, I have now worked out that I can put the measure in as a page filter and then filter out the "::". So simple and yet I missed it.
Thank you for your help.
@amitchandakThe solutions you have linked are excellent, in something like a KPI they work flawlessly. The base issue upon filtering within a pivot table remains.
In effect these measure create string values and when filtering down if there is no information for a specific user they reflect the string without the time data. So, in my case it was ::, in the first link you provided it shows as " Hours & Minutes & Seconds".
What I need is a way to filter my pivot table to exclude the specific string value of "Hours & Minutes & Seconds" or "::".
But, I have now worked out that I can put the measure in as a page filter and then filter out the "::". So simple and yet I missed it.
Thank you for your help.
@Saarek , Thanks for sharing, I was having a doubt why did that filter did not work on the measure( it seems like a column def) when everything is right. If this is working at page level if should work in measure too.
Anyway, you got the solution as of now. 🙂
@Saarek , are you trying to add duration here ?
because calculate (chat[duration], filter(..)) is column not measure
to sum duration refer
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
if not
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |