Forum Discussion

jjwerkman's avatar
jjwerkman
Regular Visitor
5 years ago

Quick Measure (time table) stops grouping customer

Need a little bit help.  I count (sum) different (this moment 3) time tables. The first 2 tables are working as intended. The table shows the customer (once) with the calculated time. When I add the...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

jjwerkman ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

jjwerkman's avatar
jjwerkman
Regular Visitor
5 years ago

Hi amitchandak thanks for the quick response. I'm not shure how to mask or delete sensitive data. I'm a newby and need to use Google a lot 🙂
I can export as table, but I'm not sure if that is wat you mean. A little bit more explanation: the customer name and the available hours (first column) are fetched from software A, the other 3 columns are fetched from software B.

The used DAX for TD_Beheer and TD_Operationeel is (same, but fetched from different tables):

TD_Beheer =
VAR Beheer = CALCULATE(SUM(IncidentTimeRegistrations_table[timeSpent_inSeconds]), FILTER(IncidentTypes, IncidentTypes[name] = "Beheer"))

VAR Hours = INT ( Beheer / 3600)
VAR Minutes = INT ( MOD( Beheer - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds = ROUNDUP(MOD ( MOD( Beheer - ( Hours * 3600 ),3600 ), 60 ),0)

// 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
IF (
Beheer <> BLANK(),
CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S )))),
BLANK()
)