Forum Discussion
Matrix returning rows with no data once text and numeric values are concatnated
Hello community,
I have matrix that has measure duration in HH:MM:SS format , once I add this measure it returns all rows dimension. On below screenshot row A shouls not be displayed. If i remove this measure row A is not displayed, it seems like once I concat text and number measure show all rows. Any way to supress row A with the duration format I need.
Sam01 -
You can use an IF statement - something like this, which checks to see whether Call Duration is blank. If it's blank, thn this measure will also be blank:
Call Duration (HH:MM:SS) = IF(ISBLANK([Call Duration]),BLANK(), VAR hours = ROUNDDOWN ( [Call Duration] / 3600, 0 ) VAR minutes = ROUNDDOWN ( MOD ( [Call Duration], 3600 ) / 60, 0 ) VAR seconds = INT ( MOD ( [Call Duration], 60 ) ) RETURN FORMAT(hours,"00") & ":" & FORMAT(minutes, "00") & ":" & FORMAT(seconds, "00") )
8 Replies
Sam01 -
You can use an IF statement - something like this, which checks to see whether Call Duration is blank. If it's blank, thn this measure will also be blank:
Call Duration (HH:MM:SS) = IF(ISBLANK([Call Duration]),BLANK(), VAR hours = ROUNDDOWN ( [Call Duration] / 3600, 0 ) VAR minutes = ROUNDDOWN ( MOD ( [Call Duration], 3600 ) / 60, 0 ) VAR seconds = INT ( MOD ( [Call Duration], 60 ) ) RETURN FORMAT(hours,"00") & ":" & FORMAT(minutes, "00") & ":" & FORMAT(seconds, "00") )
There's an easier way to do this.
Call duration (HH:MM:SS) = FORMAT(TIME(0,0,'Fact'[Call Duration]), "HH:mm:ss")
Unfortunately, due to integer limits, if you have any durations of 32,768 seconds or more, this does break. If you're not going to get calls over 9 hours, 6 minutes, and 7 seconds, then this is simple and works great.
If you need the error handling, or do expect durations longer than 09:06:07, then this will handle durations of up to 86,399 seconds (a second less than 24 hours):
Call duration (HH:MM:SS) = FORMAT(TIME(INT(numsecs/3600),MOD(INT(numsecs/60),60),MOD(numsecs,60)), "HH:mm:ss")
If you need to be able to handle durations of over 24 hours, you can still make it work, essentially using your setup, and just set a visual level filter on your call duration measure, showing items when the value is not "::"
