Matrix returning rows with no data once text and numeric values are concatnated

[Sam01]

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.

Measures:

Call Duration = CALCULATE(SUM('Fact'[CallDuration]), 'Fact'[Metric]="Calls") 

Call Duration (HH:MM:SS) =

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")

[Cmcmahan]

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 "::"

[Sam01]

@Cmcmahan  Thanks for your reply, I tried with format it did not work so I went with other route, other think I was also filtering out :: but it filtered out other necessary data where there were not callduration.

[Anonymous · Accepted solution]

@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")
)

[Sam01]

@Anonymous  there are no blank values on this Call duration columns. this measure is returning all team member  dimemsion regardless if it has data or not.

[Anonymous]

@Sam01 - I think you want this measure to be blank if call duration is blank, correct? 

[Sam01]

Yes, and I would also expect that row would not appear. If I remove my measure with formatting, charts is displaying fine. So I am thinking this measure is causing some iisues

[Anonymous]

@Sam01  - You marked my answer as solution - is everything resolved now?

[Sam01]

Yes, I was able to make ISblank to work, and its fine now. thank you for your support.